在Oracle中的SQL查询中获取固定数量的行

小编典典

在Oracle中的SQL查询中获取固定数量的行

sql

请帮助我在Oracle数据库中编写一个SQL查询。有一个名为tbl的表，它有12行。我想先选择前4行，然后选择下4行和后4行。

任何人都可以告诉我如何在Informix中执行此操作。

阅读 204

2021-04-14

共1个答案

小编典典

您可以使用rownum：

select * from (select t.*, rownum rn from tbl t) where rn between 1 and 4;
/
select * from (select t.*, rownum rn from tbl t) where rn between 5 and 8;
/
select * from (select t.*, rownum rn from tbl t) where rn between 9 and 12;
/

如果您正在使用order by子句，请使用row_number()（documentation）

select * from (select t.*, row_number() over (order by column_name) rn from tbl t) where rn between 1 and 4;
/
select * from (select t.*, row_number() over (order by column_name) rn from tbl t) where rn between 5 and 8;
/
select * from (select t.*, row_number() over (order by column_name) rn from tbl t) where rn between 9 and 12;
/

2021-04-14