SQL Server：如何基于Term和Year两列合并连续多行数据？

首先，咱们明确核心需求：针对每个SId、Program以及毕业信息（Grad term、Grad year），要先判断Year和Term的连续性（Term范围1-3，上一年的Term3之后接下一年的Term1视为连续），再把连续的多行合并成一条包含起始/结束时间段的记录。

原始数据示例 #

SId Program Term Year Grad term Grad year
-------------------------------------------------------------------
1 P 2 2001 3 2005
1 P 3 2001 3 2005
1 P 2 2002 3 2005
2 M 2 2002 2 2004
2 M 3 2002 2 2004

解决方案思路 #

核心是把Year和Term转换成一个连续的数值序列，通过这个序列判断行与行之间是否连续，再对连续的行分组合并：

1. 转换连续序列：用Year * 3 + Term把年份和学期转成连续数值，比如2001 Term3（20013+3=6006）之后的2002 Term1（20023+1=6007）就是连续的。
2. 标记连续分组：用窗口函数LAG获取上一行的序列值，当当前行和上一行的序列差不为1时，新建一个分组。
3. 分组聚合：对每个分组取最小/最大的Year和Term，得到起始和结束时间段。

具体SQL代码（兼容多数主流数据库） #

WITH ranked_data AS (
    SELECT 
        SId,
        Program,
        Term,
        Year,
        "Grad term" AS grad_term,
        "Grad year" AS grad_year,
        -- 生成用于判断连续的序列值
        Year * 3 + Term AS term_sequence,
        -- 标记连续分组：非连续时新建组
        SUM(CASE 
                WHEN term_sequence - LAG(term_sequence) OVER (
                    PARTITION BY SId, Program, "Grad term", "Grad year" 
                    ORDER BY Year, Term
                ) = 1 THEN 0 
                ELSE 1 
            END) OVER (
                PARTITION BY SId, Program, "Grad term", "Grad year" 
                ORDER BY Year, Term
            ) AS group_id
    FROM your_table_name -- 替换成你的实际表名
),
grouped_records AS (
    SELECT 
        SId,
        Program,
        MIN(Year) AS start_year,
        MIN(Term) AS start_term,
        MAX(Year) AS end_year,
        MAX(Term) AS end_term,
        grad_term,
        grad_year
    FROM ranked_data
    GROUP BY SId, Program, grad_term, grad_year, group_id
)
SELECT 
    SId,
    Program,
    start_year,
    start_term,
    end_year,
    end_term,
    grad_term,
    grad_year
FROM grouped_records
ORDER BY SId, start_year, start_term;

执行后的预期结果 #

SId Program start_year start_term end_year end_term grad_term grad_year
-----------------------------------------------------------------------
1   P       2001       2          2001     3        3         2005
1   P       2002       2          2002     2        3         2005
2   M       2002       2          2002     3        2         2004

关键细节说明 #

• PARTITION BY SId, Program, "Grad term", "Grad year"：确保我们只在同一个学生、同一个项目、同一个毕业信息的范围内判断连续性，不会跨无关分组。
• ORDER BY Year, Term：保证行的顺序是按年份和学期自然排列的，这样LAG才能拿到正确的上一行数据。
• 如果你的数据库有特殊类型限制，只需要调整term_sequence的计算逻辑即可，核心思路不变。

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