MySQL中查找指定日期指定时间后的下一个时段，如何优化多列比较方案？

你的思路方向是对的（通过行列转换来统一处理时间比较），但确实有更直接、性能更好的写法，不需要做全量的行列转置聚合，直接针对目标日期拆分时段后筛选即可。

核心思路 #

我们需要把当前宽表（一行3个时段列）转换成窄表（一行一个时段），然后直接筛选出大于当前时间的时段，再按时间排序取第一个，就是我们要的下一个可用时段。

针对特定日期的查询示例 #

比如你要查询第7天10:00:00之后的下一个时段，SQL可以这么写：

SELECT slot_name, slot_time
FROM (
    -- 把三个时段列拆分成行
    SELECT day, 'slot1' AS slot_name, slot1 AS slot_time FROM refill_roster WHERE day = 7
    UNION ALL
    SELECT day, 'slot2' AS slot_name, slot2 AS slot_time FROM refill_roster WHERE day = 7
    UNION ALL
    SELECT day, 'slot3' AS slot_name, slot3 AS slot_time FROM refill_roster WHERE day = 7
) AS unpivoted_slots
-- 筛选出晚于当前时间的时段
WHERE slot_time > '10:00:00'
-- 按时间升序排列，取第一个就是最近的下一个时段
ORDER BY slot_time ASC
LIMIT 1;

执行这个查询会直接返回：

+-----------+-----------+
| slot_name | slot_time |
+-----------+-----------+
| slot2     | 11:45:28  |
+-----------+-----------+

通用化参数版（支持动态日期和时间） #

如果需要经常切换目标日期和当前时间，可以用变量来实现：

-- 设置目标日期和当前时间
SET @target_day = 7;
SET @current_time = '10:00:00';

SELECT slot_name, slot_time
FROM (
    SELECT day, 'slot1' AS slot_name, slot1 AS slot_time FROM refill_roster WHERE day = @target_day
    UNION ALL
    SELECT day, 'slot2' AS slot_name, slot2 AS slot_time FROM refill_roster WHERE day = @target_day
    UNION ALL
    SELECT day, 'slot3' AS slot_name, slot3 AS slot_time FROM refill_roster WHERE day = @target_day
) AS unpivoted_slots
WHERE slot_time > @current_time
ORDER BY slot_time ASC
LIMIT 1;

优势对比你的原有写法 #

1. 性能更优：只针对目标日期的数据进行处理，不需要对全表做聚合操作，当表数据量大时差异会很明显
2. 逻辑更直观：直接拆分时段列→筛选时间→排序取首行，步骤清晰，可读性强
3. 代码更简洁：避免了大量重复的MAX(CASE WHEN...)语句，维护成本更低

边界情况处理（当日无可用时段） #

如果当前时间已经晚于当日所有时段，可以用IFNULL返回友好提示：

SET @target_day = 7;
SET @current_time = '13:00:00';

SELECT IFNULL(
    CONCAT('下一个可用时段：', slot_name, ' (', slot_time, ')'),
    '当日已无可用时段'
) AS result
FROM (
    SELECT slot_name, slot_time
    FROM (
        SELECT day, 'slot1' AS slot_name, slot1 AS slot_time FROM refill_roster WHERE day = @target_day
        UNION ALL
        SELECT day, 'slot2' AS slot_name, slot2 AS slot_time FROM refill_roster WHERE day = @target_day
        UNION ALL
        SELECT day, 'slot3' AS slot_name, slot3 AS slot_time FROM refill_roster WHERE day = @target_day
    ) AS unpivoted_slots
    WHERE slot_time > @current_time
    ORDER BY slot_time ASC
    LIMIT 1
) AS next_slot;

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