如何编写SQL查询合并同人员同职位的连续日期区间记录

你需要处理的是把同一name和desig下的连续日期区间记录合并，这是SQL里典型的**间隔与孤岛（Gaps and Islands）**问题，我来给你详细的解决方法。

原始数据表 #

期望输出表 #

通用SQL查询语句 #

WITH ranked_data AS (
    SELECT 
        name,
        desig,
        sdate,
        edate,
        -- 按姓名+职位分组，按起始日期排序生成行号
        ROW_NUMBER() OVER (PARTITION BY name, desig ORDER BY sdate) AS rn,
        -- 生成分组标识：同一连续区间的记录会得到相同的group_id
        DATE_SUB(sdate, INTERVAL ROW_NUMBER() OVER (PARTITION BY name, desig ORDER BY sdate) DAY) AS group_id
    FROM your_table_name
),
grouped_data AS (
    SELECT 
        name,
        desig,
        MIN(sdate) AS merged_sdate,
        MAX(edate) AS merged_edate
    FROM ranked_data
    GROUP BY name, desig, group_id
    ORDER BY name, merged_sdate
)
-- 生成新的序号并输出最终结果
SELECT 
    ROW_NUMBER() OVER (ORDER BY name, merged_sdate) AS `Sr no`,
    name,
    desig,
    merged_sdate AS sdate,
    merged_edate AS edate
FROM grouped_data;

代码逻辑解释 #

1. ranked_data 公共表表达式（CTE）

   • 用PARTITION BY name, desig把数据按姓名和职位拆分分组，再用ROW_NUMBER()按起始日期排序生成行号。
   • group_id是核心：如果两条记录是连续区间（后一条的起始日期是前一条结束日期的次日或当天），那么sdate - rn的计算结果会完全相同，以此把它们归为同一组。

2. grouped_data CTE

   • 按name, desig, group_id分组，取每组的最小起始日期和最大结束日期，完成连续区间的合并。

3. 最终查询

   • 用ROW_NUMBER()生成新的序号，输出符合要求的合并结果。

数据库适配提示 #

• 如果使用SQL Server，把DATE_SUB(sdate, INTERVAL rn DAY)替换为DATEADD(day, -rn, sdate)。
• 如果使用PostgreSQL，替换为sdate - INTERVAL '1 day' * rn。
• 确保sdate和edate是日期类型，若为字符串需先转换：比如MySQL用STR_TO_DATE(sdate, '%d/%m/%Y')，Oracle用TO_DATE(sdate, 'DD/MM/YYYY')。

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