MySQL按enum自定义优先级查询分组后供暖时间表各通道最新操作

现有语句的问题是先按channel分组取最大时间，未考虑day字段的优先级差异，导致通用规则（WEEKDAY）的晚时间记录覆盖了优先级更高的单日规则记录。

• 给day字段自定义优先级权重：单日枚举值（SUN/MON/TUE/WED/THU/FRI/SAT）优先级高于通用规则（WEEKDAY/WEEKEND/HOLIDAY）
• 筛选符合条件的所有记录后，先按优先级倒序、再按执行时间倒序排序
• 每个channel取排序后的第一条记录即可

WITH ranked_schedule AS (
    SELECT 
        *,
        -- 按channel分组排序，同组内优先级高的在前，同优先级时间晚的在前
        ROW_NUMBER() OVER (
            PARTITION BY channel 
            ORDER BY 
                CASE WHEN day IN ('SUN','MON','TUE','WED','THU','FRI','SAT') THEN 2 ELSE 1 END DESC,
                thetime DESC
        ) AS rn
    FROM heatingtimetable
    WHERE 
        thetime < '10:15'
        AND (
            -- 匹配当前具体星期几的单日规则：DAYOFWEEK返回1=SUN,2=MON...7=SAT
            ELT(DAYOFWEEK(CURDATE()), 'SUN','MON','TUE','WED','THU','FRI','SAT') = day
            -- 匹配通用规则：工作日匹配WEEKDAY，周末匹配WEEKEND，可自行扩展节假日逻辑
            OR (DAYOFWEEK(CURDATE()) BETWEEN 2 AND 6 AND day = 'WEEKDAY')
            OR (DAYOFWEEK(CURDATE()) IN (1,7) AND day = 'WEEKEND')
        )
)
SELECT channel, command, thetime, day 
FROM ranked_schedule 
WHERE rn = 1;

如果不支持窗口函数，可以用关联查询实现：

SELECT h.channel, h.command, h.thetime, h.day
FROM heatingtimetable h
INNER JOIN (
    SELECT 
        channel,
        -- 按优先级拼接权重和时间，取最大值对应的记录
        MAX(CONCAT(
            CASE WHEN day IN ('SUN','MON','TUE','WED','THU','FRI','SAT') THEN 2 ELSE 1 END,
            thetime
        )) AS max_sort_key
    FROM heatingtimetable
    WHERE 
        thetime < '10:15'
        AND (
            ELT(DAYOFWEEK(CURDATE()), 'SUN','MON','TUE','WED','THU','FRI','SAT') = day
            OR (DAYOFWEEK(CURDATE()) BETWEEN 2 AND 6 AND day = 'WEEKDAY')
            OR (DAYOFWEEK(CURDATE()) IN (1,7) AND day = 'WEEKEND')
        )
    GROUP BY channel
) t ON h.channel = t.channel 
AND CONCAT(CASE WHEN h.day IN ('SUN','MON','TUE','WED','THU','FRI','SAT') THEN 2 ELSE 1 END, h.thetime) = t.max_sort_key;

针对提供的测试数据，周三10:15执行查询时，WED单日规则的权重高于WEEKDAY，排序后WED的9:00记录优先级高于WEEKDAY的10:00记录，最终返回id为14的OFF记录，符合预期。

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