从 SQL 查询 Oracle 中获取记录范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10091506/
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
Get range of records from SQL query Oracle
提问by Peter Penzov
I want to use this SQL query to get only the records between 8 and 10:
我想使用此 SQL 查询仅获取 8 到 10 之间的记录：
select *
from(
SELECT a.*,rownum rn
FROM ACTIVESESSIONSLOG a
ORDER BY USERID ASC)
WHERE rn >= 8 and rn <= 10
When I implement this SQL query into pagination I get every time 1 row on the second page no matter how many rows I have configured to be displayed into the pages. Is this SQL query valid?
当我将这个 SQL 查询实现到分页时，无论我配置了多少行显示到页面中，我每次都会在第二页上获得 1 行。这个 SQL 查询有效吗？
This is the table structure:
这是表结构：
-- TABLE ACTIVESESSIONSLOG
CREATE TABLE ACTIVESESSIONSLOG(
ASESSIONID VARCHAR2(30 ) NOT NULL,
USERID VARCHAR2(30 ),
ACTIVITYSTART TIMESTAMP(6),
ACTIVITYEND TIMESTAMP(6),
ACTIVITY CLOB
)
/
Best wishes
最好的祝愿
回答by Justin Cave
rownumis applied before the ORDER BYso your query is almost certainly not doing what you expect. Your query is essentially asking for an arbitrary 3 rows and the ORDER BYisn't doing anything useful.
rownum应用在 之前，ORDER BY因此您的查询几乎肯定不会按照您的预期进行。您的查询本质上是要求任意 3 行，并且ORDER BY没有做任何有用的事情。
You could use the analytic function row_numberinstead, i.e.
您可以改用解析函数row_number，即
SELECT *
FROM (SELECT a.*,
row_number() over (order by userid asc) rn
FROM activeSessionsLog a)
WHERE rn BETWEEN 8 AND 10
which will page through the results
这将翻阅结果
SQL> ed
Wrote file afiedt.buf
1 select empno, ename, job
2 from (select e.*,
3 row_number() over (order by empno) rn
4 from emp e)
5* where rn between 1 and 3
SQL> /
EMPNO ENAME JOB
---------- ---------- ---------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
SQL> ed
Wrote file afiedt.buf
1 select empno, ename, job
2 from (select e.*,
3 row_number() over (order by empno) rn
4 from emp e)
5* where rn between 4 and 8
SQL> /
EMPNO ENAME JOB
---------- ---------- ---------
7566 JONES MANAGER
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
7782 CLARK MANAGER
7788 SCOTT ANALYST
SQL> ed
Wrote file afiedt.buf
1 select empno, ename, job
2 from (select e.*,
3 row_number() over (order by empno) rn
4 from emp e)
5* where rn between 9 and 11
SQL> /
EMPNO ENAME JOB
---------- ---------- ---------
7839 KING PRESIDENT
7844 TURNER SALESMAN
7876 ADAMS CLERK
It may be more efficient, however, to do something like this where Oracle can use the inner rownum <= 10predicate to know that it can stop sorting the data once it has identified the first 10 rows.
然而，这样做可能更有效，因为 Oracle 可以使用内部rownum <= 10谓词来知道一旦确定了前 10 行就可以停止对数据进行排序。
SELECT c.*
FROM (SELECT b.*, rownum rn
FROM (SELECT a.*
FROM activeSessionsLog a
ORDER BY userid asc) b
WHERE rownum <= 10) c
WHERE rn >= 8
回答by Alex
Since Oracle 12c you can now use offset/fetch clause.
从 Oracle 12c 开始，您现在可以使用 offset/fetch 子句。
You can now write queries like:
您现在可以编写如下查询：
SELECT *
FROM user_objects
ORDER object_name
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY
Please review this new featureand also consider the improvements in the execution plan.
请查看此新功能并考虑执行计划中的改进。
