MySQL查询24小时内空闲时段异常：仅返回忙碌时段间隙，无法展示当日首尾空闲时段

我来帮你搞定这个问题！你的核心需求是要覆盖当日完整的空闲时段——包括从00:00到第一个忙碌时段的开头，以及最后一个忙碌时段结束到24:00的部分，而原查询只处理了忙碌时段之间的中间间隙。你之前尝试用Union加虚拟行没成功，大概率是没处理好虚拟时段与真实时段的分组、排序逻辑，下面给你一个可行的解决方案：

思路说明 #

我们需要给每个课程ID（courses_id）额外添加两个虚拟时段：

1. 一个代表当日起始点的虚拟结束时间（00:00:00），让窗口函数能捕捉到从0点到第一个忙碌时段的空闲
2. 一个代表当日结束点的虚拟开始时间（24:00:00），让窗口函数能捕捉到最后一个忙碌时段到24点的空闲
   之后通过窗口函数关联前后时段，筛选出所有有效空闲区间。

完整查询代码 #

WITH busy_periods AS (
    -- 第一步：处理真实的忙碌时段，计算每个时段的结束时间
    SELECT 
        time AS busy_start,
        time + INTERVAL perclocko MINUTE AS busy_end,
        courses_id
    FROM students_courses
    UNION ALL
    -- 第二步：给每个courses_id添加当日起始的虚拟结束时段
    SELECT 
        '24:00:00' AS busy_start,
        '00:00:00' AS busy_end,
        courses_id
    FROM students_courses
    GROUP BY courses_id  -- 每个课程只加一条，避免重复
    UNION ALL
    -- 第三步：给每个courses_id添加当日结束的虚拟开始时段
    SELECT 
        '00:00:00' AS busy_start,
        '24:00:00' AS busy_end,
        courses_id
    FROM students_courses
    GROUP BY courses_id
),
ordered_time AS (
    -- 按课程分组，按忙碌开始时间排序，获取上一个时段的结束时间
    SELECT 
        *,
        LAG(busy_end) OVER (PARTITION BY courses_id ORDER BY busy_start) AS prev_busy_end
    FROM busy_periods
)
-- 筛选有效空闲时段，排除虚拟时段带来的无效全天空闲（无忙碌时段时会保留）
SELECT 
    prev_busy_end AS free_time_start,
    busy_start AS free_time_end
FROM ordered_time
WHERE prev_busy_end < busy_start
  AND NOT (prev_busy_end = '00:00:00' AND busy_start = '24:00:00')
ORDER BY courses_id, free_time_start;

关键细节解释 #

• busy_periods CTE：把真实忙碌时段和两个虚拟时段合并，确保每个课程都有日始、日末的时间锚点
• ordered_time CTE：用LAG窗口函数关联每个时段的上一个结束时间，这样就能把前后时间点拼接成空闲区间
• 最后筛选时：
  • 只保留prev_busy_end < busy_start的有效区间（排除时间重叠的情况）
  • 排除虚拟时段生成的00:00:00到24:00:00无效区间，但如果某课程当天没有任何忙碌时段，这个区间会被保留，正好符合“全天空闲”的需求

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