ORDER BY与ROW_NUMBER()执行优先级差异：同查询DB2与PostgreSQL结果不同

该问题来自不同数据库对空OVER()窗口子句的非标准实现差异，符合SQL标准的执行逻辑如下：

• SQL的逻辑执行顺序为：CTE/表扫描 → SELECT列表运算 → 外层ORDER BY排序
• 窗口函数属于SELECT列表运算阶段的逻辑，当row_number() over()没有显式指定ORDER BY规则时，行号的生成顺序依赖数据库的底层扫描顺序，属于未定义行为，不同数据库的实现可以不一致。
• DB2的实现刚好是先应用外层ORDER BY规则后计算行号，PostgreSQL则是按CTE的原始数据顺序计算行号后再执行外层排序，因此出现结果差异。

要在所有数据库中得到一致结果，只需将排序规则显式声明在窗口函数的OVER子句中即可，避免依赖数据库的非默认实现：

with mytables(id,animal) as (
                 values (1,'Cat'),(2,'Dog'),(3,'Mouse') 
)
select *,
       case 
         when animal = 'Dog' then 0
         when animal = 'Cat' then 1
         else 2
       end my_order_by,
       row_number() over(
         order by case 
           when animal = 'Dog' then 0
           when animal = 'Cat' then 1
           else 2
         end
       ) as rank 
from mytables  
order by my_order_by;

也可以通过嵌套子查询的方式先排序再计算行号，写法更简洁：

with mytables(id,animal) as (
                 values (1,'Cat'),(2,'Dog'),(3,'Mouse') 
),
sorted_data as (
  select *,
    case 
      when animal = 'Dog' then 0
      when animal = 'Cat' then 1
      else 2
    end my_order_by
  from mytables
  order by my_order_by
)
select *, row_number() over() as rank from sorted_data;

内容的提问来源于stack exchange，提问作者user2454234