题解 | 分析员工在不同项目中的绩效表现以及所属部门的平均绩效情况

select e.employee_id,department_name,performance_score
from employee_projects e join department_info d 
on e.employee_id = d.employee_id
where department_id = 1 
and performance_score > (
    select avg(performance_score) avg
    from employee_projects e join department_info d 
    on e.employee_id = d.employee_id
    where department_id = 1
    group by department_name
)
union
select e.employee_id,department_name,performance_score
from employee_projects e join department_info d 
on e.employee_id = d.employee_id
where department_id = 2 
and performance_score > (
    select avg(performance_score) avg
    from employee_projects e join department_info d 
    on e.employee_id = d.employee_id
    where department_id = 2
    group by department_name
)