如何查询员工每次团队变更的最早生效日期（含重回旧团队场景）

嗨，这个问题我熟！要解决人员重回旧团队也能正确记录每次变更的最早生效日期，关键是要识别连续的团队分配段——毕竟同一个团队如果中间插了其他团队，那再次回来就算新的一次变更了。用普通的MIN()函数之所以不行，是因为它会把同一个PersonID+TeamID的所有记录合并，没法区分非连续的分配。

这里给你一套基于窗口函数的解决方案，完美适配你的需求：

步骤说明 #

核心思路是：先给每个连续的相同团队分配标记一个分组ID，再按分组取最早生效日期。

完整SQL示例 #

假设你的表名为person_team_assignments，执行以下查询：

WITH ranked_assignments AS (
    SELECT 
        PersonID,
        TeamID,
        DateEffective,
        -- 累加标记，生成每个连续团队的分组ID
        SUM(is_new_group) OVER (PARTITION BY PersonID ORDER BY DateEffective) AS group_id
    FROM (
        SELECT 
            PersonID,
            TeamID,
            DateEffective,
            -- 标记：当前团队与上一条不同则为新分组
            CASE 
                WHEN LAG(TeamID) OVER (PARTITION BY PersonID ORDER BY DateEffective) != TeamID 
                THEN 1 
                ELSE 0 
            END AS is_new_group
        FROM person_team_assignments
    ) AS subquery
)
-- 按人员和分组取最早生效日期
SELECT 
    PersonID,
    TeamID,
    MIN(DateEffective) AS DateEffective
FROM ranked_assignments
GROUP BY PersonID, group_id, TeamID
ORDER BY PersonID, DateEffective;

逻辑拆解 #

1. 标记新分组：用LAG()窗口函数，按PersonID分组、DateEffective排序，拿到当前记录的上一条团队ID。如果当前团队和上一条不同，标记为1（新分组），否则为0。
2. 生成分组ID：用SUM()累加标记值，这样连续相同的团队会被分到同一个group_id里；如果中途切换团队再回来，新的连续段会有新的group_id。
3. 取最早日期：按PersonID、group_id、TeamID分组，用MIN(DateEffective)拿到每个分组的最早生效日期，就是你要的每次变更的起始日期。

比如你期望的结果中，Person1从TeamA→TeamB→TeamA，这三个会被分成3个不同的分组，各自取对应的最早日期，完全符合需求。

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