Oracle SQL:按ROWNUM进行过滤时应返回的结果不返回结果 [英] Oracle SQL: Filtering by ROWNUM not returning results when it should

问题描述

我要针对Oracle数据库运行以下SQL:

I have the following SQL I'm trying to run against an Oracle database:

SELECT *
FROM(SELECT crd.Request_ID,
        crd.Requested_Start_Date Scheduled_Start,
        crd.Actual_Start_Date Start_Date,
        crd.Actual_Completion_Date Finish_Date,
        crd.Status_Code
    FROM ((SELECT fcr.Request_ID,
                fcr.Requested_Start_Date,
                fcr.Actual_Start_Date,
                fcr.Actual_Completion_Date,
                fcr.Status_Code,
                fcr.Oracle_Session_ID,
                fcr.Responsibility_ID
            FROM Applsys.FND_Concurrent_Requests fcr
            WHERE fcr.Oracle_Session_ID IS NOT NULL)
        UNION ALL (SELECT xcr.Request_ID,
                xcr.Requested_Start_Date,
                xcr.Actual_Start_Date,
                xcr.Actual_Completion_Date,
                xcr.Status_Code,
                xcr.Oracle_Session_ID,
                xcr.Responsibility_ID
            FROM xxfnd.emr_FND_Concurrent_Requests xcr
            WHERE xcr.Oracle_Session_ID IS NOT NULL)) crd
    WHERE crd.Actual_Start_Date >= to_Date('06/01/2014', 'MM/DD/YYYY')
    ORDER BY 3, 1)
WHERE Rownum < (1000000 * to_Number(:X))
    AND ROWNUM >= (1000000 * (to_Number(:X)-1))

对于:X设置为1，我得到了(按预期)前999,999行数据.但是，当我将:X设置为2时，不会返回任何内容.尽管事实上当我使用Select Count(*)代替Select *并完全丢弃Where子句时，结果仍表明子查询返回了超过900万条记录.

For :X set to 1, I get (as expected) the first 999,999 rows of data. However, when I set :X to 2, nothing is returned. This despite the fact that when I use Select Count(*) in place of Select * and drop the Where clause entirely the results indicate there are over 9 million records returned by the subquery.

任何帮助将不胜感激.

推荐答案

您不能像这样使用rownum. rownum是在查询中实际返回值时计算的-仅当返回一行时.因此，该语句:

You cannot use rownum like this. rownum is calculated when the values are actually returned from the query -- only when a row is returned. Hence, the statement:

where rownum = 2

将永远不会返回值，因为在"2"之前必须有一个"1".

will never return a value, because there needs to a be a "1" before a "2".

如果使用的是Oracle 12+，则可以将offset子句与fetch first <n> rows only一起使用.在早期版本中，可以使用row_number() over ()将行号计算为列，并在where中使用它.

If you are using Oracle 12+, you can use the offset clause with fetch first <n> rows only. In earlier versions, you can use row_number() over () to calculate a row number as a column and use that in the where.

实际上，您的查询已使用子查询，因此您可以执行以下操作:

Actually, your query already uses a subquery, so you can do something like:

select *
from (select . . .,
             row_number() over (order by Request_ID, Actual_Start_Date) as rn
      . . .
     ) t
WHERE rn < (1000000 * to_Number(:X) and)
      rn >= (1000000 * (to_Number(:X)-1))

这篇关于Oracle SQL:按ROWNUM进行过滤时应返回的结果不返回结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！