按INTRAWORKNO筛选适配度最高员工的SQL查询需求

要实现需求，核心逻辑是用员工能处理的排班编号总数作为适配度，筛选每个排班编号下适配度最高的员工。以下是两种可行的查询方案：

方案一：基于CTE关联筛选 #

先统计每个员工的适配度，再计算每个排班编号的最高适配度，最后关联筛选符合条件的员工：

WITH EmployeeCapability AS (
    -- 统计每个员工能处理的INTRAWORKNO总数（适配度）
    SELECT 
        employee, 
        COUNT(INTRAWORKNO) AS CNTINTRAWORKNO
    FROM @persqualintrawork 
    GROUP BY employee
),
IntraWorkMaxCapability AS (
    -- 计算每个INTRAWORKNO对应的最高适配度
    SELECT 
        p.INTRAWORKNO,
        MAX(e.CNTINTRAWORKNO) AS MaxCapability
    FROM @persqualintrawork p
    JOIN EmployeeCapability e ON p.employee = e.employee
    GROUP BY p.INTRAWORKNO
)
-- 筛选每个INTRAWORKNO中适配度等于最高值的员工
SELECT 
    p.INTRAWORKNO,
    p.employee
FROM @persqualintrawork p
JOIN EmployeeCapability e ON p.employee = e.employee
JOIN IntraWorkMaxCapability m ON p.INTRAWORKNO = m.INTRAWORKNO
WHERE e.CNTINTRAWORKNO = m.MaxCapability
ORDER BY p.INTRAWORKNO, p.employee;

方案二：使用窗口函数简化查询 #

通过RANK()窗口函数直接为每个排班编号下的员工按适配度排名，取排名第一的员工：

WITH EmployeeCapability AS (
    -- 统计每个员工的适配度
    SELECT 
        employee, 
        COUNT(INTRAWORKNO) AS CNTINTRAWORKNO
    FROM @persqualintrawork 
    GROUP BY employee
),
RankedEmployees AS (
    -- 为每个INTRAWORKNO下的员工按适配度降序排名
    SELECT 
        p.INTRAWORKNO,
        p.employee,
        RANK() OVER (PARTITION BY p.INTRAWORKNO ORDER BY e.CNTINTRAWORKNO DESC) AS Rank
    FROM @persqualintrawork p
    JOIN EmployeeCapability e ON p.employee = e.employee
)
-- 取排名第一的员工（适配度最高）
SELECT INTRAWORKNO, employee
FROM RankedEmployees
WHERE Rank = 1
ORDER BY INTRAWORKNO, employee;

结果验证 #

两种方案都会得到符合需求的结果：

• INTRAWORKNO 0：保留员工1、2（适配度均为1，无更优替代）
• INTRAWORKNO 1：保留员工40、45（适配度为3）
• INTRAWORKNO 2：仅保留员工40、45（适配度3，高于50、55的适配度2）
• INTRAWORKNO 3：仅保留员工40、45（同INTRAWORKNO 2逻辑）

内容的提问来源于stack exchange，提问作者Lukas Fürst