MySQL中如何在CASE WHEN语句内使用ROW_NUMBER()自增列

SQL同层级SELECT子句中定义的列别名，无法被同一层级的其他计算表达式直接引用——这是SQL标准执行顺序决定的：窗口函数计算、列别名生成属于SELECT阶段的执行步骤，同阶段的其他表达式读取不到刚生成的别名，因此会抛出Unknown column 'row_' in field list的报错。

方案1：嵌套一层查询先计算行号（推荐） #

用CTE或者子查询先把带row_行号的基础结果集计算完成，再在外层查询中引用row_写CASE WHEN逻辑，这种写法可维护性最好，调整行号规则时只需要修改一处：

-- CTE写法，可读性更高
WITH base_data AS (
    SELECT
        ROW_NUMBER() OVER (ORDER BY RDate DESC) AS row_,
        -- 替换为你实际需要查询的字段，比如col1、col2等
        col1,
        col2
    FROM tblr R
    JOIN tblrn RR ON R.RUniqueID = RR.WRID
    WHERE WHID = @VHID AND RDate < @RD
)
SELECT
    row_,
    col1,
    col2,
    CASE WHEN row_ = 1 THEN 8 ELSE 0 END AS stat1,
    CASE WHEN row_ = 2 THEN 2 ELSE 0 END AS stat2
FROM base_data
ORDER BY RDate DESC;

如果不习惯用CTE，也可以换成普通子查询，执行逻辑完全一致：

SELECT
    row_,
    col1,
    col2,
    CASE WHEN row_ = 1 THEN 8 ELSE 0 END AS stat1,
    CASE WHEN row_ = 2 THEN 2 ELSE 0 END AS stat2
FROM (
    SELECT
        ROW_NUMBER() OVER (ORDER BY RDate DESC) AS row_,
        col1,
        col2
    FROM tblr R
    JOIN tblrn RR ON R.RUniqueID = RR.WRID
    WHERE WHID = @VHID AND RDate < @RD
) t
ORDER BY RDate DESC;

注意：窗口函数里的排序规则必须和最外层ORDER BY的规则完全一致，否则会出现行号和最终展示顺序不匹配、stat字段赋值错误的问题。

方案2：同层重复写窗口函数表达式 #

如果不想嵌套查询，可以直接在CASE WHEN里重复写完整的ROW_NUMBER()窗口函数逻辑，不需要引用别名，执行效果和方案1一致：

SELECTSELECT
    ROW_NUMBER() OVER (ORDER BY RDate DESC) AS row_,
    col1,
    col2,
    CASE WHEN ROW_NUMBER() OVER (ORDER BY RDate DESC) = 1 THEN 8 ELSE 0 END AS stat1,
    CASE WHEN ROW_NUMBER() OVER (ORDER BY RDate DESC) = 2 THEN 2 ELSE 0 END AS stat2
FROM tblr R
JOIN tblrn RR ON R.RUniqueID = RR.WRID
WHERE WHID = @VHID AND RDate < @RD
ORDER BY RDate DESC;

这种写法的缺点是窗口函数逻辑重复了三次，如果后续需要调整排序规则，必须同步修改三处，漏改就会出现逻辑错误，仅适合简单临时查询场景使用。

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