时间戳

首页 > 解决方案 > max(row_num) 给我的结果与 oracle 中的 count(*) 不同。这是我的查询

oracle - max(row_num) 给我的结果与 oracle 中的 count(*) 不同。这是我的查询

问题描述

select count(*)
from (SELECT Instance.JOB_INSTANCE_ID,
             Instance.JOB_NAME,
             Instance.JOB_KEY,
             Execution.JOB_EXECUTION_ID,
             Execution.VERSION,
             Execution.CREATE_TIME,
             Execution.START_TIME,
             Execution.END_TIME,
             Execution.STATUS,
             Execution.EXIT_CODE,
             Execution.EXIT_MESSAGE,
             Execution.LAST_UPDATED,
             Execution.JOB_CONFIGURATION_LOCATION
      FROM (SELECT JOB_INSTANCE_ID,
                   JOB_NAME,
                   JOB_KEY
            FROM PROCESOSBATCH_OWN.BATCH_JOB_INSTANCE
            order by JOB_INSTANCE_ID) Instance,
           (SELECT JOB_EXECUTION_ID,
                   VERSION,
                   JOB_INSTANCE_ID,
                   CREATE_TIME,
                   START_TIME,
                   END_TIME,
                   STATUS,
                   EXIT_CODE,
                   EXIT_MESSAGE,
                   LAST_UPDATED,
                   JOB_CONFIGURATION_LOCATION
            FROM PROCESOSBATCH_OWN.BATCH_JOB_EXECUTION
            WHERE 1 = 1
            order by JOB_INSTANCE_ID) Execution
      where Instance.JOB_INSTANCE_ID=Execution.JOB_INSTANCE_ID
      and Execution.JOB_INSTANCE_ID is not null);

  COUNT(*)
----------
      9689

select max(row_num)
from (SELECT Execution.row_num,
             Instance.JOB_INSTANCE_ID,
             Instance.JOB_NAME,
             Instance.JOB_KEY,
             Execution.JOB_EXECUTION_ID,
             Execution.VERSION,
             Execution.CREATE_TIME,
             Execution.START_TIME,
             Execution.END_TIME,
             Execution.STATUS,
             Execution.EXIT_CODE,
             Execution.EXIT_MESSAGE,
             Execution.LAST_UPDATED,
             Execution.JOB_CONFIGURATION_LOCATION
      FROM (SELECT JOB_INSTANCE_ID,
                   JOB_NAME,
                   JOB_KEY
            FROM PROCESOSBATCH_OWN.BATCH_JOB_INSTANCE
            order by JOB_INSTANCE_ID) Instance,
           (SELECT rownum as row_num,
                   JOB_EXECUTION_ID,
                   VERSION,
                   JOB_INSTANCE_ID,
                   CREATE_TIME,
                   START_TIME,
                   END_TIME,
                   STATUS,
                   EXIT_CODE,
                   EXIT_MESSAGE,
                   LAST_UPDATED,
                   JOB_CONFIGURATION_LOCATION
            FROM PROCESOSBATCH_OWN.BATCH_JOB_EXECUTION
            WHERE 1 = 1
            order by JOB_INSTANCE_ID) Execution
      where Instance.JOB_INSTANCE_ID=Execution.JOB_INSTANCE_ID
      and Execution.JOB_INSTANCE_ID is not null);

MAX(ROW_NUM)
------------
        9854

标签： oraclecount

解决方案

我们没有计算最终结果的最大值rownum。我们row_num在其中一个内部查询中定义rownum as row_num，在其之上添加了更多过滤器。

假设这个内部查询返回了 10k 行，max(row_num)这里将返回 10000。
从 2000 到 3000 的行号在我们的主查询中被过滤掉。由于我们已经定义rownum as row_num,max(row_num)仍然会返回 10000，同时max(rownum)在同一个查询中会返回 9000。

一般来说，max(rownum)会给出与count(*)相同查询相同的结果。情况并非如此，我们使用的是 tiffingmax(row_num)而不是max(rownum)，这可能会有所不同。

编码快乐！☺</p>

推荐阅读