sql (oracle) 选择前 10 条记录，然后是接下来的 10 条，依此类推
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3869311/
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
sql (oracle) to select the first 10 records, then the next 10, and so on
提问by llcf
I figure I might just be missing some obvious syntax but what is the sql (oracle) to select the first 10 records, then the next 10, and so on?
我想我可能只是遗漏了一些明显的语法，但是选择前 10 条记录，然后是接下来的 10 条等等的 sql (oracle) 是什么？
I tried using rownum but can seem to get rownum > X and rownum < Y to work.
我尝试使用 rownum 但似乎可以让 rownum > X 和 rownum < Y 工作。
llcf
液晶显示器
回答by Thilo
There is only a rather convoluted way to do this, which is a real pain with Oracle. They should just implement a LIMIT/OFFSET clause...
只有一种相当复杂的方法可以做到这一点，这对 Oracle 来说是一个真正的痛苦。他们应该只实现一个 LIMIT/OFFSET 子句......
The rownum gets assigned afterthe row has been selected by the where clause, so that a rownum must always start with 1. where rownum > xwill always evaluate to false.
在 where 子句选择行后分配 rownum ，因此 rownum 必须始终以 1 开头。where rownum > x将始终评估为 false。
Also, rownum gets assigned before sorting is done, so the rownum will not be in the same order as your order by says.
此外， rownum在排序完成之前被分配，因此 rownum 的顺序与您的 order by 的顺序不同。
You can get around both problems with a subselect:
您可以通过子选择解决这两个问题：
select a,b,c, rn from
( select a,b,c, rownum rn from
( select a,b,c from the_table where x = ? order by c)
where rownum < Y)
where rn > X
If you do not need to sort (but only then), you can simplify to
如果您不需要排序（但仅在那时），您可以简化为
select a,b,c, rn from
( select a,b,c, rownum rn from the_table where rownum < Y )
where rn > X
回答by Dino
You could use ROW_NUMBER() function... for example
您可以使用 ROW_NUMBER() 函数...例如
SELECT *
FROM ( SELECT A.*, ROW_NUMBER( ) OVER (ORDER BY MYFIELD) AS MYROW FROM MYTABLE A )
WHERE MYROW < X
SELECT *
FROM ( SELECT A.*, ROW_NUMBER( ) OVER (ORDER BY MYFIELD) AS MYROW FROM MYTABLE A )
WHERE MYROW BETWEEN X AND Y
SELECT *
FROM ( SELECT A.*, ROW_NUMBER( ) OVER (ORDER BY MYFIELD) AS MYROW FROM MYTABLE A )
WHERE MYROW BETWEEN Y AND Z
回答by Karl Bartel
You could also select all rows, and only fetch 10 at a time. This works only if you can keep the cursor between the fetches, of course.
您还可以选择所有行，一次只提取 10 行。当然，这仅在您可以将光标保持在两次提取之间时才有效。
