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

SELECT
    a.employee_id,
    b.employee_name,
    a.performance_score as first_half_2024_score,
    ROW_NUMBER() OVER (PARTITION BY c.project_name ORDER BY a.performance_score DESC,a.employee_id ASC) AS project_group_rank,
    b.department,
    c.project_name as project_group
FROM
    performance as a 
    INNER JOIN employees as b ON a.employee_id = b.employee_id
    INNER JOIN projects as c ON a.project_id = c.project_id
WHERE
    c.start_date BETWEEN '2024-01-01' AND '2024-06-30'
ORDER BY c.project_id ASC,project_group_rank ASC

ROW_NUMBER() OVER (PARTITION BY c.project_name ORDER BY a.performance_score DESC,a.employee_id ASC)

先按照绩效排名 再按照员工id排名

如果只按照绩效排名 第五六行是颠倒的

where和窗口函数可以同时存在一个查询中 但是where不能查询窗口函数 不要混淆