从 SQL 查询 Oracle 中获取记录范围 [英] Get range of records from SQL query Oracle
问题描述
我想使用这个 SQL 查询来只获取 8 到 10 之间的记录:
I want to use this SQL query to get only the records between 8 and 10:
select *
from(
SELECT a.*,rownum rn
FROM ACTIVESESSIONSLOG a
ORDER BY USERID ASC)
WHERE rn >= 8 and rn <= 10
当我在分页中实现这个 SQL 查询时,无论我配置了多少行显示到页面中,我每次都会在第二页上获得 1 行.这个 SQL 查询有效吗?
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?
这是表结构:
-- TABLE ACTIVESESSIONSLOG
CREATE TABLE ACTIVESESSIONSLOG(
ASESSIONID VARCHAR2(30 ) NOT NULL,
USERID VARCHAR2(30 ),
ACTIVITYSTART TIMESTAMP(6),
ACTIVITYEND TIMESTAMP(6),
ACTIVITY CLOB
)
/
最好的祝福
推荐答案
rownum
应用在 ORDER BY
之前,因此您的查询几乎肯定不会按照您的预期执行.您的查询本质上要求任意 3 行,而 ORDER BY
没有做任何有用的事情.
rownum
is applied before the ORDER BY
so your query is almost certainly not doing what you expect. Your query is essentially asking for an arbitrary 3 rows and the ORDER BY
isn't doing anything useful.
您可以使用解析函数 row_number
代替,即
You could use the analytic function row_number
instead, i.e.
SELECT *
FROM (SELECT a.*,
row_number() over (order by userid asc) rn
FROM activeSessionsLog a)
WHERE rn BETWEEN 8 AND 10
将翻阅结果
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
然而,这样做可能更有效,因为 Oracle 可以使用内部 rownum <= 10
谓词来知道一旦确定了第一个数据,它就可以停止对数据进行排序10 行.
It may be more efficient, however, to do something like this where Oracle can use the inner rownum <= 10
predicate to know that it can stop sorting the data once it has identified the first 10 rows.
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
这篇关于从 SQL 查询 Oracle 中获取记录范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!