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

with t as
(select
    e.employee_id,
    e.employee_name,
    pe.performance_score as first_half_2024_score,
    e.department,
    pr.project_id,
    pr.project_name as project_group
from employees e
left join performance pe on e.employee_id=pe.employee_id
left join projects pr on pe.project_id=pr.project_id
where year(pr.start_date)=2024 and month(pr.start_date) between 1 and 6)

select 
    employee_id,
    employee_name,
    first_half_2024_score,
    row_number() over(partition by project_group order by first_half_2024_score desc) as project_group_rank,
    department,
    project_group
from t
order by project_id,project_group_rank,employee_id;