SQL重复记录问题：员工团队变动时无法获取重复团队的最小开始日期与最大结束日期

Hey Rajesh, I get exactly what you're dealing with—when an employee moves between teams and circles back to the same one later, standard grouping might either lump all their tenure together (if you just group by employee and team) or fail to correctly aggregate dates for each separate stint. Let's break down the solution step by step.

先明确场景与假设表结构 #

假设你的数据表（比如叫employee_team_assignments）包含这些核心字段：

• employee_id：唯一标识员工的ID
• team_id：唯一标识团队的ID
• start_date：员工加入该团队的日期
• end_date：员工离开该团队的日期（NULL通常表示当前仍在该团队任职）

情况1：合并同一员工在同一团队的所有任职日期 #

如果你的需求是不管中间有没有离开过，只要是同一个团队，就取该员工在这个团队的最早开始日期和最晚结束日期，那用简单的分组聚合就能解决：

SELECT
    employee_id,
    team_id,
    MIN(start_date) AS min_start_date,
    MAX(COALESCE(end_date, CURRENT_DATE)) AS max_end_date
FROM employee_team_assignments
GROUP BY employee_id, team_id
ORDER BY employee_id, team_id;

这里用COALESCE处理end_date为NULL的情况，替换成当前日期（如果业务需要的话），确保MAX()能拿到有效日期值。

情况2：区分同一员工在同一团队的多次独立任职（间隙与岛屿问题） #

如果员工离开团队后又重新加入，你需要分别获取每次独立任职的起止日期（比如第一次在A团队的 tenure、第二次回到A团队的 tenure），这时候需要用窗口函数识别“连续任职岛屿”：

步骤1：给连续的团队任职标记分组 #

我们可以用ROW_NUMBER()窗口函数，分别按员工全局排序、按员工+团队排序，两者的差值会帮我们区分不同的任职阶段：

WITH ranked_assignments AS (
    SELECT
        employee_id,
        team_id,
        start_date,
        end_date,
        -- 按员工+入职日期排序的全局行号
        ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY start_date) AS global_row,
        -- 按员工+团队+入职日期排序的行号
        ROW_NUMBER() OVER (PARTITION BY employee_id, team_id ORDER BY start_date) AS team_row
    FROM employee_team_assignments
),
assignment_groups AS (
    SELECT
        *,
        -- 差值相同的记录属于同一连续任职阶段
        global_row - team_row AS group_id
    FROM ranked_assignments
)

步骤2：按分组聚合日期 #

现在每个group_id对应员工在同一团队的一次连续任职，我们可以分组得到每次任职的起止日期：

SELECT
    employee_id,
    team_id,
    MIN(start_date) AS min_start_date,
    MAX(COALESCE(end_date, CURRENT_DATE)) AS max_end_date
FROM assignment_groups
GROUP BY employee_id, team_id, group_id
ORDER BY employee_id, start_date;

为什么之前的方法可能失效？ #

如果之前你只做了GROUP BY employee_id, team_id，当员工多次回到同一团队时，会把所有 tenure 的日期合并，无法区分不同的任职阶段；而如果直接查询原始数据，又只能得到每条单独的记录，没法聚合每次任职的完整起止日期。上面的“间隙与岛屿”解法正好填补了这个空白。

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