sql - Oracle Select查询请帮忙

Question

SELECT  id
      FROM    ( 
 SELECT  id
      FROM   table      
      WHERE   
      PROCS_DT is null
      ORDER BY prty desc, cret_dt ) where rownum >0 and rownum <=100 

上面的查询按预期返回了 100 条记录

 SELECT  id
          FROM    ( 
     SELECT  id
          FROM   table      
          WHERE   
          PROCS_DT is null
          ORDER BY prty desc, cret_dt ) where rownum >101 and rownum <=200 

为什么上述查询返回零记录？

有人可以帮助我如何继续下去吗？我对甲骨文很笨...

Answer 1

试试这个:

SELECT id
FROM
  (SELECT id,
          rownum AS rn
   FROM
     (SELECT id
      FROM TABLE
      WHERE PROCS_DT IS NULL
      ORDER BY prty DESC, cret_dt) )
WHERE rn >101
  AND rn <=200

如果您习惯使用 ANALYTIC 函数，请尝试以下操作:

SELECT id
  FROM 
    (
        SELECT id,
                ROW_NUMBER() OVER(ORDER BY prty DESC, cret_dt ) rn
            FROM table
        WHERE procs_dt IS NULL
    )
 WHERE rn >101 and rn <=200