题解 | 分析每个员工在不同项目中的绩效情况

-- 这个题目的核心就是要求出员工在所属项目的绩效排名,直接关联用窗口函数即可
SELECT
    t1.employee_id,
    t1.employee_name,
    t2.performance_score first_half_2024_score,
    ROW_NUMBER() OVER(partition by t3.project_name ORDER BY t2.performance_score DESC) project_group_rank,
    t1.department,
    t3.project_name project_group
FROM employees t1
LEFT JOIN performance t2
ON t1.employee_id = t2.employee_id
LEFT JOIN projects t3
ON t2.project_id = t3.project_id
WHERE t3.start_date BETWEEN '2024-01-01' AND '2024-06-30'
ORDER BY t2.project_id,project_group_rank,t1.employee_id