如何用SQL查询员工最新EmployeeGroup变更记录及初始组信息

有一张存储员工信息的表，被外部服务用于计算员工时薪。表中EmployeeGroup字段为员工分组属性，同组员工享有相同的时薪，当EmployeeGroup发生变更时，员工时薪需同步调整。

需求：

• 提取每位员工的最新EmployeeGroup变更记录，忽略该变更之前的所有记录；
• 若员工从未变更过EmployeeGroup，则返回其所属组的第一条记录。

示例说明：

• 员工ID123的John Smith多次变更分组，需返回其2023-06-20变更为Seniors的记录；
• 员工ID789的Brody James仅变更职位未变更分组，需返回其入职时的初始分组记录。

尝试过使用LAG()窗口函数编写CTE查询，但无法筛选出符合要求的记录，且因需要返回约30个其他字段，无法使用GROUP BY语句。尝试的SQL如下：

WITH PreviousEmployeeChanges AS (
    SELECT
       EmployeeId,
       LAG(EmployeeGroup) OVER (PARTITION BY EmployeeId ORDER BY EmployeeSKey) PrevSubgroup,
       LAG(StartDate) OVER (PARTITION BY EmployeeId ORDER BY EmployeeSKey) AS PrevStartDate,
       ROW_NUMBER() OVER (PARTITION BY EmployeeId ORDER BY EmployeeSKey desc) AS rn 
    FROM
        Employees
)
SELECT e.*, pec.PrevSubgroup, pec.PrevStartDate,
COALESCE(pec.PrevStartDate, e.startDate) AS LastChangedEmployeeGroup
FROM
    Employees e
JOIN
    PreviousEmployeeChanges pec ON e.employeeId = pec.employeeId 

附测试数据INSERT语句：

INSERT INTO Employees (EmployeeId, FirstName, LastName, Position, StartDate, EmployeeGroup, EmployeeSKey, ActionDesc)
VALUES
    (123, 'John', 'Smith', 'Intern', '2016-01-01', 'Interns', 1, 'Hired'),
    (123, 'John', 'Smith', 'Jnr Dev', '2018-01-01', 'Juniors', 2, 'Absorbed'),
    (123, 'John', 'Smith', 'Jnr Dev', '2019-07-01', 'Juniors', 4, 'Team Change'),
    (123, 'John', 'Smith', 'Mid-Level Dev', '2021-01-01', 'Mid-Levels', 5, 'Promotion'),
    (123, 'John', 'Smith', 'Senior Dev', '2023-06-20', 'Seniors', 9, 'Promotion'),
    (123, 'John', 'Smith', 'Senior Dev', '2024-05-24', 'Seniors', 11, 'Manager Change'),
    (456, 'Sally', 'Jones', 'Jnr Researcher', '2022-01-07', 'Junior', 6, 'Hired'),
    (789, 'Brody', 'James', 'N Wing Janitor', '2018-01-01', 'Mid-Levels', 3, 'Hired'),
    (789, 'Brody', 'James', 'S Wing Janitor', '2022-01-01', 'Mid-Levels', 7, 'Restructure'),
    (101, 'Paul', 'Gaib', 'Junior VP', '2023-06-01', 'Jnr Executives', 8, 'Nepotism'),
    (101, 'Paul', 'Gaib', 'Senior VP', '2024-01-01', 'Snr Executives', 10, 'Nepotism');

以下SQL可以满足需求，无需使用GROUP BY即可返回所有字段：

WITH EmployeeGroupChanges AS (
    SELECT
        *,
        -- 标记当前记录是否为分组变更（与上一条记录的分组不同）
        CASE WHEN LAG(EmployeeGroup) OVER (PARTITION BY EmployeeId ORDER BY EmployeeSKey) != EmployeeGroup THEN 1 ELSE 0 END AS IsGroupChange,
        -- 按员工分组，记录正序编号（用于取初始记录）
        ROW_NUMBER() OVER (PARTITION BY EmployeeId ORDER BY EmployeeSKey ASC) AS rn_asc
    FROM Employees
),
LastGroupChangeRecords AS (
    SELECT
        EmployeeId,
        -- 获取每个员工最后一次分组变更的记录编号
        MAX(CASE WHEN IsGroupChange = 1 THEN EmployeeSKey END) AS LastChangeSKey
    FROM EmployeeGroupChanges
    GROUP BY EmployeeId
)
SELECT egc.*
FROM EmployeeGroupChanges egc
JOIN LastGroupChangeRecords lgc ON egc.EmployeeId = lgc.EmployeeId
-- 筛选逻辑：有分组变更则取最后一次变更记录，无变更则取初始记录
WHERE (lgc.LastChangeSKey IS NOT NULL AND egc.EmployeeSKey = lgc.LastChangeSKey)
   OR (lgc.LastChangeSKey IS NULL AND egc.rn_asc = 1);

逻辑说明 #

1. EmployeeGroupChanges：给每条员工记录标记是否属于分组变更（通过LAG()对比当前与前一条记录的EmployeeGroup），同时给每个员工的记录按EmployeeSKey正序编号，用于后续取初始记录。
2. LastGroupChangeRecords：统计每个员工最后一次分组变更对应的EmployeeSKey，如果员工从未变更分组，则该值为NULL。
3. 最终查询：根据是否存在分组变更记录，筛选出目标行——有变更则取最后一次变更的记录，无变更则取员工的第一条记录。

测试结果 #

执行该SQL后，将得到符合需求的结果：

• 员工123：返回EmployeeSKey=9的记录（2023-06-20变更为Seniors）
• 员工789：返回EmployeeSKey=3的记录（初始Mid-Levels分组）
• 员工456：返回唯一的入职记录
• 员工101：返回EmployeeSKey=10的记录（2024-01-01变更为Snr Executives）

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