SQL Server查询优化：特定状态员工记录筛选求助

你的查询当前存在以下问题：

• 未对T状态记录做60天时间范围过滤，导致超期记录被保留
• 当员工同时存在A/T状态时，分组逻辑无法优先保留A状态的最新记录
• 多条T状态记录时，仅通过MAX聚合取日期，但无法确保只保留单条最新记录

使用窗口函数ROW_NUMBER()对每个员工的记录分组排序，结合状态规则实现精准筛选：

WITH RankedAssignments AS (
    SELECT 
        e.EMPLOYEENUMBER,
        c.DESCRIPTION,
        a.STATUS,
        a.TIMESTAMP AS LASTDATE,
        -- 按员工分组，排序规则：A/L状态优先，同状态下取最新日期
        ROW_NUMBER() OVER (
            PARTITION BY e.EMPLOYEENUMBER
            ORDER BY 
                CASE WHEN a.STATUS IN ('A', 'L') THEN 0 ELSE 1 END,
                a.TIMESTAMP DESC
        ) AS rn
    FROM dbo.EMPLOYEE e
    INNER JOIN dbo.ASSIGNMENT a ON e.ID = a.EMPLOYEE_ID
    INNER JOIN dbo.ASSIGNMENT_ATTRIBUTEINSTANCE aa ON a.ID = aa.Assignment_ID
    INNER JOIN dbo.ATTRIBUTEINSTANCE ai ON ai.ID = aa.attributeInstances_ID
    INNER JOIN dbo.CODE c ON ai.VALUE = c.CODE
    -- 先过滤超期T记录，保留所有A/L记录
    WHERE 
        c.CODEGROUP_ID = 4979
        AND (
            a.STATUS IN ('A', 'L')
            OR (a.STATUS = 'T' AND a.TIMESTAMP >= DATEADD(DAY, -60, GETDATE()))
        )
)
SELECT EMPLOYEENUMBER, DESCRIPTION, STATUS, LASTDATE
FROM RankedAssignments
-- 每个员工仅保留优先级最高的一条记录
WHERE rn = 1;

1. CTE预处理：
   • 通过WHERE子句直接过滤掉60天前的T状态记录，减少后续计算量
   • 用ROW_NUMBER()按员工编号分组，排序时让A/L状态优先于T，同状态下按日期倒序，确保符合需求的记录排在第一位
2. 最终筛选：
   • 仅取每个员工分组中rn=1的记录，即最优匹配记录

• 同时有A和T状态的员工（如0019）：A状态的rn为1，自动保留最新的A记录
• 多条有效T状态的员工（如0023）：仅保留日期最新的那条T记录
• 超期T状态员工（如0016/0017/0018）：直接在预处理阶段被过滤

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