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

select e.employee_id
, d.department_name
, e.performance_score
from employee_projects e
left join department_info d
on e.employee_id = d.employee_id
left join 
(select department_name ,round(avg(performance_score),2) avg_performance_score
from(
select e.employee_id
, project_id
, performance_score
, department_name
from employee_projects e
left join department_info d
on e.employee_id = d.employee_id)a
group by 1)b
on d.department_name = b.department_name
where e.performance_score > b.avg_performance_score