如何在UPDATE语句中结合CASE表达式使用列别名——将sales_data表的udf字段更新为调整时间后的星期值

数据老陈

2026-4-27

解决方案：将SELECT逻辑转换为UPDATE语句

没问题，你已经把核心的计算逻辑写对了，只需要把这个逻辑迁移到UPDATE语句的SET子句里，就能实现更新udf字段的需求。

基础版UPDATE语句

直接复用你原SELECT里的CASE逻辑，写法简单直观：

UPDATE sales_data
SET udf = CASE 
    WHEN WEEKDAY(SUBTIME(date_of_sale, '6:0:0')) = 0 THEN 'Sunday' 
    WHEN WEEKDAY(SUBTIME(date_of_sale, '6:0:0')) = 1 THEN 'Monday' 
    WHEN WEEKDAY(SUBTIME(date_of_sale, '6:0:0')) = 2 THEN 'Tuesday' 
    WHEN WEEKDAY(SUBTIME(date_of_sale, '6:0:0')) = 3 THEN 'Wednesday' 
    WHEN WEEKDAY(SUBTIME(date_of_sale, '6:0:0')) = 4 THEN 'Thursday' 
    WHEN WEEKDAY(SUBTIME(date_of_sale, '6:0:0')) = 5 THEN 'Friday' 
    WHEN WEEKDAY(SUBTIME(date_of_sale, '6:0:0')) = 6 THEN 'Saturday' 
END;

优化版（减少重复计算）

上面的写法会重复计算WEEKDAY(SUBTIME(...))7次，当数据量很大时，可以优化成只计算一次（MySQL 8.0+支持CTE写法）：

WITH adjusted_day_calc AS (
    SELECT 
        id,
        WEEKDAY(SUBTIME(date_of_sale, '6:0:0')) AS day_number
    FROM sales_data
)
UPDATE sales_data s
JOIN adjusted_day_calc adc ON s.id = adc.id
SET s.udf = CASE 
    WHEN adc.day_number = 0 THEN 'Sunday' 
    WHEN adc.day_number = 1 THEN 'Monday' 
    WHEN adc.day_number = 2 THEN 'Tuesday' 
    WHEN adc.day_number = 3 THEN 'Wednesday' 
    WHEN adc.day_number = 4 THEN 'Thursday' 
    WHEN adc.day_number = 5 THEN 'Friday' 
    WHEN adc.day_number = 6 THEN 'Saturday' 
END;