从 SQL 查询 Oracle 中获取记录范围 [英] Get range of records from SQL query Oracle

查看:82
本文介绍了从 SQL 查询 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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆