SQL带分组Row Number实现：按公司对连续日期分组生成编号问题

该需求可通过SQL实现，属于SQL经典的「间隙与岛屿」数据分组场景，核心逻辑是识别同一员工下、同公司且日期连续的记录作为同一个分组，公司变更或日期中断后自动生成新的分组序号。

期望结果参考 #

实现代码（兼容所有支持标准窗口函数的数据库，如MySQL8.0+、PostgreSQL、SQL Server、Oracle等） #

WITH mark_group_boundary AS (
    -- 第一步：标记每一行是否为新分组的起点
    SELECT 
        *,
        CASE 
            WHEN Company = LAG(Company) OVER (PARTITION BY EmpNo ORDER BY StartDt)
                 AND DATE_ADD(LAG(EndDt) OVER (PARTITION BY EmpNo ORDER BY StartDt), INTERVAL 1 DAY) = StartDt
            THEN 0 
            ELSE 1 
        END AS is_new_group
    FROM 你的业务表名
),
calc_group_id AS (
    -- 第二步：对分组起点标记累加求和，得到最终分组序号
    SELECT 
        *,
        SUM(is_new_group) OVER (PARTITION BY EmpNo ORDER BY StartDt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_id
    FROM mark_group_boundary
)
-- 查询最终结果
SELECT EmpNo, Company, StartDt, EndDt, group_id AS `Desired Result`
FROM calc_group_id;

适配说明 #

• 若使用SQL Server，需将DATE_ADD语法替换为DATEADD(day, 1, LAG(EndDt) OVER (PARTITION BY EmpNo ORDER BY StartDt))
• 若日期字段带有时分秒，需先将字段转为纯日期类型再做比较，避免时分秒差异导致分组判断错误
• 多员工场景下PARTITION BY EmpNo会自动按员工隔离分组序号，无需额外修改逻辑

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