选择前150行，然后选择下150行，依此类推? [英] Select the first 150 rows, then the next 150 and so on?

问题描述

如何在oracle sql中的表中选择前x行，然后选择下x行，依此类推?我知道我可以使用TOP/LIMIT，然后得到第一个x

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

select a from b limit 150 =>获取前150行.

select a from b limit 150 => get the first 150 rows.

为什么?我想将前150个输出复制到一个文件中，然后再将其150个复制到另一个文件中，依此类推...

Why? I would like to copy the first 150 outputs into a file, then the next 150 into another file and so on...

推荐答案

在Oracle中，您有一个不错的rownum:它是一个伪列.它对结果集中的记录编号.满足select语句中where条件的第一个记录被赋予rownum=1，满足相同条件的每个后续记录将被赋予rownum.

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.

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;

(感谢@Mark Ba​​nnister)

(thanks to @Mark Bannister)

如果将ORDER BY子句嵌入到子查询中，并将ROWNUM条件放置在顶级查询中，则可以强制ROWNUM条件在对行进行排序之后应用.

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.

这篇关于选择前150行，然后选择下150行，依此类推?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！