MySQL如何按规则合并重叠会议时段统计会议室使用时长

假设你的原始记录表名为meeting_records，包含字段id、room_id、started_at、ended_at、created_by，可按以下方案实现时段合并，无需额外写循环存储过程。

方案1：MySQL 8.0+ 版本（推荐，用递归CTE实现） #

WITH RECURSIVE sorted_records AS (
    -- 按房间分组，组内按会议开始时间升序排序，生成连续行号
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY room_id ORDER BY started_at ASC) AS rn
    FROM meeting_records
),
merged_periods AS (
    -- 递归初始化：取每个房间的第一条记录作为初始合并时段
    SELECT 
        room_id,
        rn,
        started_at AS FSA,
        ended_at AS FEA
    FROM sorted_records
    WHERE rn = 1
    UNION ALL
    -- 逐行处理后续记录，按规则合并时段
    SELECT 
        s.room_id,
        s.rn,
        CASE 
            WHEN s.started_at > mp.FEA THEN s.started_at
            ELSE mp.FSA
        END AS FSA,
        CASE 
            WHEN s.started_at BETWEEN mp.FSA AND mp.FEA AND s.ended_at > mp.FEA THEN s.ended_at
            WHEN s.started_at > mp.FEA THEN s.ended_at
            ELSE mp.FEA
        END AS FEA
    FROM merged_periods mp
    JOIN sorted_records s ON s.room_id = mp.room_id AND s.rn = mp.rn + 1
)
-- 去重后得到所有不重叠的有效时段
SELECT DISTINCT room_id, FSA AS 时段开始时间, FEA AS 时段结束时间
FROM merged_periods
ORDER BY room_id, FSA;

执行后直接返回你需要的4条合并后时段记录。

方案2：MySQL 5.x 版本（用用户变量实现） #

SELECT 
    room_id,
    MIN(started_at) AS 时段开始时间,
    MAX(ended_at) AS 时段结束时间
FROM (
    SELECT 
        *,
        -- 判断是否为新的不重叠时段
        @new_group := IF(started_at > @prev_fea, 1, 0) AS is_new,
        -- 更新当前合并时段的最大结束时间
        @prev_fea := IF(@new_group = 1, ended_at, GREATEST(@prev_fea, ended_at)) AS current_fea,
        -- 生成分组ID，同个合并时段的记录分组ID相同
        @group_id := IF(@new_group = 1, @group_id + 1, @group_id) AS group_id
    FROM meeting_records
    JOIN (SELECT @prev_fea := '1970-01-01', @group_id := 0, @new_group := 0) AS vars
    ORDER BY room_id, started_at ASC
) AS t
GROUP BY room_id, group_id
ORDER BY room_id, group_id;

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