主页 / dba / 问题 / 149796
Accepted
Pierre-Alain Vigeant
Asked: 2016-09-16 07:27:56 +0800 CST

使用 ROWNUM 的查询在每次执行时返回不同的行

  • 772

我有一个查询，用于搜索我们网络中的子部门列表。当查询在没有任何过滤器的情况下执行时，每次执行都会返回不同数量的行

SELECT DISTINCT 
    t1.ZRESUBDIV,
    t1.SUBDIV_NAME
FROM M_STAGE_WP_PFUNCT_LOC t1
WHERE ROWNUM <= 200  -- normally a filter goes here
ORDER BY t1.SUBDIV_NAME

执行 1

ZRESUBDIV SUBDIV_NAME                                                
------------ -------------------------------------- ----------------------
第262章                                                  
400全水                                                  
第671章                                                    
第372章                                                        
第277章                                                   
200桥                                                      
第374章                                                     
第543章                                               
第208章                                                     
第324章                                                     
340芝加哥                                                     
...

执行 2

ZRESUBDIV SUBDIV_NAME                                                
------------ -------------------------------------- ----------------------
第670章                                                     
第262章                                                  
400全水                                                  
第671章                                                    
第372章                                                        
第277章                                                   
第604章                                                   
200桥                                                      
第607章                                                     
第354章                                                        
第543章                                               
...

还缺少数据：尽管巴吞鲁日细分在数据中，但从未出现过。

如果我删除 ROWNUM，所有数据都会正确返回。我需要保留 ROWNUM，因为查询支持 WHERE 子句，并在细分名称上进行过滤。

这是什么原因？

oracle oracle-12c

2 个回答

  1. Best Answer

    ROWNUM之前被评估过ORDER BY，那不是ROWNUM应该如何使用的。要ROWNUM正确使用，请使用如下查询：

    SELECT * FROM 
(
  SELECT DISTINCT 
      t1.ZRESUBDIV,
      t1.SUBDIV_NAME
  FROM M_STAGE_WP_PFUNCT_LOC t1
  ORDER BY t1.SUBDIV_NAME
)
WHERE ROWNUM <= 200;

    但这是旧的。由于您使用的是 12c，请使用以下命令：

    SELECT DISTINCT 
    t1.ZRESUBDIV,
    t1.SUBDIV_NAME
FROM M_STAGE_WP_PFUNCT_LOC t1
ORDER BY t1.SUBDIV_NAME
FETCH FIRST 200 ROWS ONLY;
    • 10

  2. ROWNUM是做这种查询的错误方式。正如@Kris Johnston 在评论中用“cart and the horse”明喻解释的那样：该WHERE子句在 之前执行ORDER BY，如果没有 ，您无法保证结果的顺序ORDER BY，所以是的，每次运行都会产生不同的结果。

    您可以使用ROW_NUMBER()and 子查询来代替：

    SELECT
    t.ZRESUBDIV,
    t.SUBDIV_NAME
FROM
  ( SELECT 
        t1.ZRESUBDIV,
        t1.SUBDIV_NAME,
        ROW_NUMBER() OVER (ORDER BY t1.SUBDIV_NAME) AS RN
    FROM M_STAGE_WP_PFUNCT_LOC t1
    WHERE --- your filter goes here but NOT any ROWNUM
    GROUP BY
        t1.ZRESUBDIV,
        t1.SUBDIV_NAME
  ) t
WHERE
    t.RN <= 200
ORDER BY
    t.SUBDIV_NAME ;

    内部ORDER BY定义将选择哪些行（因为前 200 行将具有从 1 到 200 的 row_numbers），而外部ORDER BY的 wcich 是可选的，仅与呈现顺序有关。

    请注意，排序不是全部，即您可能有相同的行SUBDIV_NAME。如果您希望完全指定订单，您可以将其更改ORDER BY为ORDER BY SUBDIV_NAME, BYZRESUBDIV（例如）。

    @Balazs Papp 的答案中的其他 2 个查询也很好，如果不是更好的话。第一个可能被广泛使用，因为它在ROW_NUMBER()实施之前适用于旧版本。只要你是 12c 版本，第二个肯定是你能得到的最简洁的。

    • 7

相关问题