这是个经典的**连续相同值分组（岛屿问题）**场景，咱们可以借助窗口函数轻松解决。先明确下你的输入数据和需求：

输入数据 #

需求目标 #

按ID排序，将同一ID下连续出现的相同Type归为一组，生成该组的Start Date（组内最早日期）和End Date（下一组开始日期的前一天；如果是最后一组，则用组内最晚日期）。

解决方案代码 #

核心思路是用两个行号的差值来标记连续的Type组（也就是“岛屿”），再基于分组计算起止日期：

WITH ranked_data AS (
    SELECT 
        ID,
        Type,
        Date,
        -- 按ID分组、日期排序的全局行号
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Date) AS global_rn,
        -- 按ID+Type分组、日期排序的行号
        ROW_NUMBER() OVER (PARTITION BY ID, Type ORDER BY Date) AS type_rn
    FROM your_table_name  -- 替换成你的实际表名
),
grouped_islands AS (
    SELECT 
        ID,
        Type,
        MIN(Date) AS start_date,
        MAX(Date) AS temp_end_date
    FROM ranked_data
    -- 差值相同的记录属于同一连续Type组
    GROUP BY ID, Type, (global_rn - type_rn)
)
SELECT 
    ID,
    Type,
    start_date,
    -- 取下一组最早日期的前一天作为当前组结束日，无下组则用自身最晚日期
    COALESCE(
        (SELECT DATEADD(day, -1, MIN(start_date)) 
         FROM grouped_islands g2 
         WHERE g2.ID = g1.ID AND g2.start_date > g1.temp_end_date),
        g1.temp_end_date
    ) AS end_date
FROM grouped_islands g1
ORDER BY ID, start_date;

代码解释 #

1. ranked_data CTE：生成两个行号：

   • global_rn：同一ID下按日期排序的全局行号，每条记录递增1
   • type_rn：同一ID+Type下按日期排序的行号，仅当Type相同时递增
     连续的相同Type记录，global_rn - type_rn的结果会保持一致，这个差值就是咱们用来分组的“岛屿标识”。

2. grouped_islands CTE：按ID、Type和岛屿标识分组，计算每组的最早日期（start_date）和最晚日期（temp_end_date）。

3. 主查询：通过关联子查询找到当前组之后的下一组最早日期，减1天得到当前组的end_date；如果是该ID的最后一组，就用自身的temp_end_date作为结束日。

数据库适配说明 #

不同数据库的日期函数略有差异，需要调整DATEADD部分：

• MySQL：用DATE_SUB(MIN(start_date), INTERVAL 1 DAY)替代DATEADD(day, -1, MIN(start_date))
• Oracle：用MIN(start_date) - INTERVAL '1' DAY
• SQL Server：保持DATEADD(day, -1, MIN(start_date))即可

最终输出结果 #

执行后会得到符合预期的结果：

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