MySQL CASE语句引用列别名sessionDate报错问题求解

问题原因 #

MySQL的SQL语句执行顺序为：FROM/JOIN -> WHERE -> GROUP BY -> 聚合函数 -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT
你在SELECT语句中定义的别名sessionDate要到SELECT阶段才会正式生成，而同层级SELECT里的CASE语句和别名定义属于同一执行阶段，MySQL不会提前识别该别名，因此会报字段不存在的错误。

解决方法 #

方法1：直接复用IF逻辑（全版本兼容） #

直接把CASE语句里引用的sessionDate替换成定义别名的完整IF表达式即可，无需调整整体查询结构，适合简单查询场景：

IF (csm_pat_exe_date.pedate_date IS NULL, ses_date, csm_pat_exe_date.pedate_date) AS sessionDate,
CASE WHEN DAYOFWEEK(IF (csm_pat_exe_date.pedate_date IS NULL, ses_date, csm_pat_exe_date.pedate_date)) = 1 THEN 'Sun'
    WHEN DAYOFWEEK(IF (csm_pat_exe_date.pedate_date IS NULL, ses_date, csm_pat_exe_date.pedate_date)) = 2 THEN 'Mon'
    WHEN DAYOFWEEK(IF (csm_pat_exe_date.pedate_date IS NULL, ses_date, csm_pat_exe_date.pedate_date)) = 3 THEN 'Tue'
    WHEN DAYOFWEEK(IF (csm_pat_exe_date.pedate_date IS NULL, ses_date, csm_pat_exe_date.pedate_date)) = 4 THEN 'Wed'
    WHEN DAYOFWEEK(IF (csm_pat_exe_date.pedate_date IS NULL, ses_date, csm_pat_exe_date.pedate_date)) = 5 THEN 'Thu'
    WHEN DAYOFWEEK(IF (csm_pat_exe_date.pedate_date IS NULL, ses_date, csm_pat_exe_date.pedate_date)) = 6 THEN 'Fri'
    WHEN DAYOFWEEK(IF (csm_pat_exe_date.pedate_date IS NULL, ses_date, csm_pat_exe_date.pedate_date)) = 7 THEN 'Sat'
    ELSE ''
END AS weekday

方法2：使用子查询封装别名 #

把生成sessionDate的逻辑放在内层子查询中，外层查询可直接使用别名做CASE运算，代码可读性更高，适合多字段复杂查询场景：

SELECT 
    t.sessionDate,
    CASE WHEN DAYOFWEEK(t.sessionDate) = 1 THEN 'Sun'
        WHEN DAYOFWEEK(t.sessionDate) = 2 THEN 'Mon'
        WHEN DAYOFWEEK(t.sessionDate) = 3 THEN 'Tue'
        WHEN DAYOFWEEK(t.sessionDate) = 4 THEN 'Wed'
        WHEN DAYOFWEEK(t.sessionDate) = 5 THEN 'Thu'
        WHEN DAYOFWEEK(t.sessionDate) = 6 THEN 'Fri'
        WHEN DAYOFWEEK(t.sessionDate) = 7 THEN 'Sat'
        ELSE ''
    END AS weekday
FROM (
    SELECT 
        IF (csm_pat_exe_date.pedate_date IS NULL, ses_date, csm_pat_exe_date.pedate_date) AS sessionDate
        -- 此处补充原查询需要的其他字段、表关联规则
    FROM 你的业务表名
    -- 此处补充原查询的WHERE、GROUP BY等逻辑
) AS t

方法3：使用CTE公用表表达式（MySQL 8.0+支持） #

逻辑和子查询一致，写法更清晰，适合多层嵌套的复杂查询场景：

WITH temp_table AS (
    SELECT 
        IF (csm_pat_exe_date.pedate_date IS NULL, ses_date, csm_pat_exe_date.pedate_date) AS sessionDate
        -- 此处补充原查询需要的其他字段、表关联规则
    FROM 你的业务表名
    -- 此处补充原查询的WHERE、GROUP BY等逻辑
)
SELECT 
    sessionDate,
    CASE WHEN DAYOFWEEK(sessionDate) = 1 THEN 'Sun'
        WHEN DAYOFWEEK(sessionDate) = 2 THEN 'Mon'
        WHEN DAYOFWEEK(sessionDate) = 3 THEN 'Tue'
        WHEN DAYOFWEEK(sessionDate) = 4 THEN 'Wed'
        WHEN DAYOFWEEK(sessionDate) = 5 THEN 'Thu'
        WHEN DAYOFWEEK(sessionDate) = 6 THEN 'Fri'
        WHEN DAYOFWEEK(sessionDate) = 7 THEN 'Sat'
        ELSE ''
    END AS weekday
FROM temp_table

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