基于过往共同任务表现筛选最优工作团队的SQL查询实现

原查询存在的核心问题 #

• 多人任务筛选逻辑失效：原CTE中按EmployeeID + AssignmentID分组后做HAVING COUNT(...) > 1的判断，由于每个员工和任务的指派关系本身仅存在1条记录，count结果永远为1，根本无法过滤掉单人参与的任务
• 平均耗时计算维度错位：查询中同时混用了普通GROUP BY聚合和窗口函数，窗口函数的分区粒度没有对齐“员工全部协作任务”的统计范围，最终算出来的平均结果实际上是单条任务本身的耗时，不是多任务的均值
• 语法层面存在bug：派生子查询末尾多了多余逗号、引用了未定义的M.TaskID字段，实际执行时会直接抛出语法错误

修正后的查询实现 #

按照筛选要求拆分逻辑层，分步完成任务过滤、耗时计算、均值统计的全流程：

WITH MultiParticipantAssignment AS (
    -- 第一步：按任务维度统计参与人数，过滤掉单人任务
    SELECT 
        AssignmentID
    FROM Work.EmployeeAssignment
    GROUP BY AssignmentID
    HAVING COUNT(DISTINCT EmployeeID) >= 2
),
TaskWithDuration AS (
    -- 第二步：关联任务表，计算每个协作任务的实际耗时
    SELECT 
        EA.EmployeeID,
        EA.AssignmentID,
        DATEDIFF(y, A.StartingDate, A.EndingDate) AS TaskDuration
    FROM Work.EmployeeAssignment EA
    INNER JOIN MultiParticipantAssignment MPA 
        ON EA.AssignmentID = MPA.AssignmentID
    INNER JOIN Work.Assignment A 
        ON EA.AssignmentID = A.AssignmentID
),
EmployeeCollabAvg AS (
    -- 第三步：按员工维度，统计其所有参与协作任务的平均耗时
    SELECT
        EmployeeID,
        AVG(TaskDuration) AS AvgCollabDuration
    FROM TaskWithDuration
    GROUP BY EmployeeID
)
-- 关联员工基础信息，输出最终结果
SELECT 
    TWD.EmployeeID,
    TWD.AssignmentID,
    E.FirstName + ' ' + E.LastName AS EmployeeName,
    ECA.AvgCollabDuration AS AverageTime
FROM TaskWithDuration TWD
INNER JOIN EmployeeCollabAvg ECA
    ON TWD.EmployeeID = ECA.EmployeeID
INNER JOIN Work.Employee E
    ON TWD.EmployeeID = E.EmployeeID
-- 去重：保证同一任务下每个员工仅展示1条记录
GROUP BY 
    TWD.EmployeeID,
    TWD.AssignmentID,
    E.FirstName + ' ' + E.LastName,
    ECA.AvgCollabDuration
ORDER BY AverageTime, TWD.AssignmentID

匹配预期要求的效果说明 #

• 自动排除单人参与的任务：比如原实际输出中EmployeeID=4单独参与的AssignmentID=2任务，会在第一步CTE中被过滤，不会出现在最终结果里
• 平均耗时计算完全匹配规则：比如Employee4、Employee5共同参与了耗时6的Assignment3、耗时14的Assignment1，两者的平均耗时统一为(6+14)/2 = 10，和预期输出的数值完全一致
• 无重复条目：每个AssignmentID下，参与任务的员工仅保留1条对应记录，不会出现重复行

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