同一月份同职位有两名员工时，如何筛选保留离职员工A的记录？

针对你提到的「同月同职位下离职员工和入职员工共存时保留离职员工记录」的需求，我们可以通过窗口函数优先筛选离职员工的方式实现，修改后的SQL如下：

SELECT 
    PERSON_ID,
    TITLE,
    START_DATE,
    END_DATE,
    ISNULL(END_DATE, CAST(GETDATE() AS DATE)) AS ED,
    DESCRIPTION,
    PeriodID,
    CalDateStart,
    CalDateEnd
FROM (
    SELECT 
        H.[PERSON_ID],
        H.[TITLE],
        H.[START_DATE],
        H.[END_DATE],
        ISNULL(H.END_DATE, CAST(GETDATE() AS DATE)) AS ED,
        H.[DESCRIPTION],
        Per.PeriodID,
        Per.CalDateStart,
        Per.CalDateEnd,
        -- 同职位同月度内，优先保留当月离职的员工
        ROW_NUMBER() OVER (
            PARTITION BY H.TITLE, Per.PeriodID
            ORDER BY 
                CASE 
                    WHEN H.END_DATE BETWEEN Per.CalDateStart AND Per.CalDateEnd THEN 1 
                    ELSE 0 
                END DESC,
                H.START_DATE DESC
        ) AS rn
    FROM H
    INNER JOIN Period Per
        ON ISNULL(H.END_DATE, CAST(GETDATE() AS DATE)) >= Per.CalDateStart
        AND H.START_DATE <= CAST(Per.CalDateEnd AS DATE)
    WHERE PERSON_ID IN (73, 81)
) AS temp
-- 只保留同职位同月度的目标记录
WHERE rn = 1

关键逻辑说明 #

• 分组排序：用PARTITION BY H.TITLE, Per.PeriodID按职位和月度分组，确保只在同一职位的同一月度内做筛选
• 优先规则：通过CASE表达式标记当月离职的员工（END_DATE落在月度区间内），让这类员工排序优先级最高
• 筛选结果：外层只取每组的第一条记录（rn=1），确保同职位同月度下只会保留符合要求的离职员工，无离职员工时则保留入职时间最晚的在职员工（可根据实际需求调整ORDER BY后的规则）

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