嵌套SELECT中使用ROW_NUMBER()作为CASE条件时结果异常问题

执行以下SQL时出现不符合预期的结果：

SELECT
    *,
    CASE 
        WHEN rownum = 1 THEN 1
        ELSE 0
    END AS col3
FROM
    (SELECT
         col1, col2,
         ROW_NUMBER() OVER (PARTITION BY col2 ORDER BY col1) AS rownum
     FROM
         table
    )

预期每个以col2分区的分组内，第一行的col3值为1，其余行col3为0，但实际仅一行col3为1，其余全为0——不过子查询返回的rownum值是正确的，具体结果如下：

问题根源 #

rownum是Oracle等数据库的内置伪列，属于保留关键字。当子查询把ROW_NUMBER()的结果命名为rownum时，外层查询的CASE判断会优先调用数据库内置的ROWNUM，而非你定义的别名。内置ROWNUM是全局行号，仅在查询结果的行生成时逐行赋值，导致只有某一行会被判定为ROWNUM=1，和分区内的行号逻辑完全不符。

修复方法 #

把rownum替换成非保留关键字的别名，比如row_num，修改后的SQL如下：

SELECT
    *,
    CASE 
        WHEN row_num = 1 THEN 1
        ELSE 0
    END AS col3
FROM
    (SELECT
         col1, col2,
         ROW_NUMBER() OVER (PARTITION BY col2 ORDER BY col1) AS row_num
     FROM
         table
    )

修改后，外层查询会正确引用子查询生成的分区行号，每个col2分组内的第一行col3都会被设为1，符合预期。

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