SQL 选择前 150 行，然后选择接下来的 150 行，依此类推？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9631371/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select the first 150 rows, then the next 150 and so on?
提问by sabisabi
How can I select in oracle sql in a Table the first x rows, then the next x and so on? I know I could use TOP/LIMIT, then I get the first x
如何在表中的 oracle sql 中选择前 x 行，然后选择下 x 行，依此类推？我知道我可以使用 TOP/LIMIT，然后我得到第一个 x
select a from b limit 150=> get the first 150 rows.
select a from b limit 150=> 获取前 150 行。
Edit: Why? I would like to copy the first 150 outputs into a file, then the next 150 into another file and so on...
编辑：为什么？我想将前 150 个输出复制到一个文件中，然后将下一个 150 个输出复制到另一个文件中，依此类推...
回答by xbonez
LIMIT 150or LIMIT 0,150: first 150 rows
LIMIT 150或LIMIT 0,150：前 150 行
LIMIT 150,150: next 150 rows
LIMIT 150,150: 下 150 行
LIMIT 300,150: next 150 rows
LIMIT 300,150: 下 150 行
and so on
等等
回答by vulkanino
In Oracle you have the nice rownum: it is a pseudo column. It numbers the records in a result set. The first record that meets the where criteria in a select statement is given rownum=1, and every subsequent record meeting that same criteria increases rownum.
在 Oracle 中你有一个很好的rownum：它是一个伪列。它对结果集中的记录进行编号。给定满足 select 语句中 where 条件的第一条记录，rownum=1随后满足相同条件的每个记录都会增加rownum。
SELECT
a, b
FROM
(SELECT rownum rn, a, b from table WHERE c=some_value ORDER BY some_column)
WHERE
rn BETWEEN 150 AND 300;
(thanks to @Mark Bannister)
（感谢@Mark Bannister）
If you embed the ORDER BY clause in a subquery and place the ROWNUM condition in the top-level query, then you can force the ROWNUM condition to be applied after the ordering of the rows.
如果在子查询中嵌入 ORDER BY 子句并将 ROWNUM 条件放在顶级查询中，则可以强制在行排序后应用 ROWNUM 条件。
回答by Flukey
I assume you're trying to do pagination, if so you can do it like this:
我假设您正在尝试进行分页，如果是这样，您可以这样做：
Let pageSizebe 150
让pageSize是150
SELECT * FROM
(
SELECT a.*, rownum r__
FROM
(
SELECT * FROM tblName c
) a
WHERE rownum < ((pageNumber * pageSize) + 1 )
)
WHERE r__ >= (((pageNumber-1) * pageSize) + 1)
回答by Upadhyay Puneet
- First get id of the last element from your top selection rows by:
- 首先通过以下方式从顶部选择行中获取最后一个元素的 id：
(a) SQL Query as follows:
(a) SQL 查询如下：
Select top 1 id from (SELECT TOP 150 * FROM (select top 150 * from outlet order by id asc)as d order by id desc) as b order by id asc
Save selected id in a Session object of ViewState namely LastOutletID
Declare a int PageSize = 150 and also add to Session object of ViewState
将选定的 id 保存在 ViewState 的 Session 对象中，即 LastOutletID
声明一个 int PageSize = 150 并添加到 ViewState 的 Session 对象中
(b) SqlQuery as Follow:
(b) SqlQuery 如下：
Select top 150 * from (SELECT TOP 150 * FROM (select top PageSize * from outlet where id > LastOutletID order by id asc)as d order by id desc) as b order by id asc
Select top 150 * from (SELECT TOP 150 * FROM (select top PageSize * from outlet where id > LastOutletID order by id asc)as d order by id desc) as b order by id asc
- Now if you want to select next top rows, follow step 1 to get last element id and save it then just add 150 to PageSize will give you next 151 to 300 rows
- 现在，如果您想选择下一个顶行，请按照步骤 1 获取最后一个元素 id 并保存它，然后将 150 添加到 PageSize 将为您提供下一个 151 到 300 行
回答by Lame
Solution for SQL Server: 1) Get total row count in my table.
SQL Server 的解决方案：1) 获取表中的总行数。
For eg. select count(1) from MYTABLE -- 500 rows
例如。从 MYTABLE 中选择 count(1) -- 500 行
2) Use CTE to assign logical row number for each row in my table.
2) 使用 CTE 为我的表中的每一行分配逻辑行号。
3) Select number of rows using row number interval and BETWEEN clause.
3) 使用行号间隔和 BETWEEN 子句选择行数。
WITH CTE AS (SELECT ROW_NUMBER() OVER(order by MyColumn1, MyColumn2, MyColumn3) AS Row#, t.* FROM MYTABLE t )
WITH CTE AS (SELECT ROW_NUMBER() OVER(order by MyColumn1, MyColumn2, MyColumn3) AS Row#, t.* FROM MYTABLE t )
select * from CTE where Row# between 150 and 300 Order by MyColumn1, MyColumn2, MyColumn3
select * from CTE where Row# between 150 and 300 Order by MyColumn1, MyColumn2, MyColumn3
4) Repeat steps 2) and 3) using next row number interval until row total count is reached.
4) 使用下一个行号间隔重复步骤 2) 和 3)，直到达到行总数。
回答by Bob Nightingale
Beginning with Oracle 12, you have FETCH and OFFSET.
从 Oracle 12 开始，您有 FETCH 和 OFFSET。
For your example, where you want to put 150 rows into one file and the next 150 rows into another file:
对于您的示例，您要将 150 行放入一个文件并将接下来的 150 行放入另一个文件：
Spool file1.txt
select a from b
order by a
Fetch first 150 rows only;
Spool off
Spool file2.txt
select a from b
offset 150 rows
Fetch next 150 rows only;
spool off
This linkshows the railroad track diagram of the row limiting clause from the Oracle 12.2 documentation.
此链接显示了 Oracle 12.2 文档中行限制子句的铁路轨道图。
The examples herealso include "order by" in them, which makes a lot of sense if you are looking for the TOP of something.
此处的示例中还包含“order by”，如果您正在寻找某事物的 TOP，这很有意义。
Previous versions of Oracle would have required the use of rownum and order-by as described by other answers here.
如此处其他答案所述，以前版本的 Oracle 将需要使用 rownum 和 order-by。
