SQL分组查询求助：如何合并同一城市的连续时间段记录

现有名为living的表，字段包括Start、Stop、City，原始数据如下：

期望将同一城市的连续时间段记录合并为一条，得到如下结果：

但使用以下SQL语句时，会错误合并同一城市的非连续时段：

SELECT City, MIN(Start) as STA, MAX(Stop) AS STO FROM living GROUP BY City

请求正确的SQL实现方式。

这是典型的连续时间段合并问题，需要通过分组标识区分同一城市的不同连续时段，以下是两种主流实现方式：

方法1：使用窗口函数（适用于MySQL 8.0+、PostgreSQL、SQL Server等支持窗口函数的数据库） #

实现逻辑： #

1. 用LAG()窗口函数获取同城市前一条记录的Stop日期，判断当前记录的Start是否为前一条Stop的次日，以此标记新分组的起点。
2. 对标记值累计求和，生成同一连续时段的唯一分组ID。
3. 按City和分组ID聚合，取该组的最小Start和最大Stop。

完整SQL： #

WITH grouped_data AS (
    SELECT 
        Start,
        Stop,
        City,
        -- 当前记录与前一条不连续时，标记为1，累计求和得到分组ID
        SUM(CASE WHEN DATE_ADD(LAG(Stop) OVER (PARTITION BY City ORDER BY Start), INTERVAL 1 DAY) = Start THEN 0 ELSE 1 END) 
        OVER (PARTITION BY City ORDER BY Start) AS group_id
    FROM living
)
SELECT 
    MIN(Start) AS Start,
    MAX(Stop) AS Stop,
    City
FROM grouped_data
GROUP BY City, group_id
ORDER BY Start;

方法2：适用于不支持窗口函数的旧版数据库（如MySQL 5.x） #

实现逻辑： #

通过自连接统计每个记录之前的不连续记录数量，以此作为分组标识，再按分组聚合合并时段。

完整SQL： #

SELECT 
    MIN(l1.Start) AS Start,
    MAX(l1.Stop) AS Stop,
    l1.City
FROM living l1
LEFT JOIN living l2 
    ON l1.City = l2.City 
    AND l2.Start <= l1.Start 
    AND DATE_ADD(l2.Stop, INTERVAL 1 DAY) >= l1.Start
GROUP BY l1.City, l1.Start, (SELECT COUNT(*) FROM living l3 WHERE l3.City = l1.City AND l3.Stop < DATE_SUB(l1.Start, INTERVAL 1 DAY))
ORDER BY Start;

说明： #

• 方法1效率更高、可读性更强，优先推荐使用支持窗口函数的数据库。
• 以上代码默认以「当前记录Start是前一条Stop的次日」作为连续判断标准，若你的业务允许其他间隔规则，可调整日期判断逻辑。

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