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

select employee_id,employee_name ,first_half_2024_score,
row_number()over(partition by project_group order by first_half_2024_score desc,employee_id asc) as project_group_rank,
department,project_group
 from 
(select employee_id,employee_name ,sum(performance_score) as first_half_2024_score,
department,project_name as project_group

from performance
join projects using(project_id)
join employees using(employee_id)
where start_date between '2024-01-01' and '2024-07-01'
group by employee_id,employee_name ,project_name) a 
order by project_group asc,project_group_rank asc,employee_id asc

原来排名的时候还得按照员工ID降序才能实现，因为有的员工绩效相同。这样才能和答案一致，对照半天