如何对CASE语句输出的别名结果使用WHERE子句执行过滤操作

问题原因 #

SQL的逻辑执行顺序决定了SELECT子句中定义的别名无法在WHERE子句中调用。SQL的标准执行顺序优先级如下：

• FROM / JOIN 读取表数据
• WHERE 过滤行
• GROUP BY 分组
• HAVING 过滤分组结果
• SELECT 计算返回字段、生成别名
• ORDER BY 排序

你定义的namevalue别名是在SELECT阶段才生成的，而WHERE执行时这个别名还不存在，因此会报字段不存在的错误；而ORDER BY执行顺序晚于SELECT，因此可以正常调用别名。

解决方案 #

方案1：直接将CASE逻辑写入WHERE子句

最简单的修复方式，直接把CASE的判断逻辑移到WHERE中即可，不需要修改其他部分逻辑：

SELECT 
    id, name, 
    CASE 
        WHEN name = 'foo' THEN true
        WHEN name = 'bar' THEN false
        ELSE NULL
    END AS namevalue
FROM 
    case_in_where
WHERE
    CASE 
        WHEN name = 'foo' THEN true
        WHEN name = 'bar' THEN false
        ELSE NULL
    END IS NOT NULL
ORDER BY
    namevalue

方案2：用子查询包裹原查询逻辑

把生成别名的逻辑放在内层子查询中，外层查询就可以直接调用别名做过滤：

SELECT * FROM (
    SELECT 
        id, name, 
        CASE 
            WHEN name = 'foo' THEN true
            WHEN name = 'bar' THEN false
            ELSE NULL
        END AS namevalue
    FROM 
        case_in_where
) t
WHERE namevalue IS NOT NULL
ORDER BY namevalue

方案3：用CTE（公共表达式）定义中间结果

可读性更好的写法，适合逻辑更复杂的场景：

WITH temp_data AS (
    SELECT 
        id, name, 
        CASE 
            WHEN name = 'foo' THEN true
            WHEN name = 'bar' THEN false
            ELSE NULL
        END AS namevalue
    FROM 
        case_in_where
)
SELECT * FROM temp_data
WHERE namevalue IS NOT NULL
ORDER BY namevalue

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