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

with s1 as(
    select e.employee_id as employee_id,
    e.performance_score as performance_score,
    d.department_id as department_id,
    d.department_name as department_name
    from employee_projects e join department_info d using(employee_id)),
    s2 as(
    select department_id,department_name,round(avg(performance_score),0) as avg_performance_score 
    from s1 
    group by department_id,department_name)
select s1.employee_id,s1.department_name,performance_score
from s1 join s2 using(department_id)
where s1.performance_score>=s2.avg_performance_score
order by s1.employee_id

解题思路：

（1）先求出：每个部门平均分——再将：平均分表和员工绩效表，通过部门id连接，——最后：筛选出：个人绩效>=平均分的即为答案。

（2）group by 问题

s2求部门平均分时，如果select和group by语句都用或都不用department_id、department_name（其中之一或两个都有，最好至少有id-唯一性，名称可能一样），就没问题

但select用了department_id和department_name，group by只用了department_id

【看下面的解释：select只能是：group by 中的列+聚合函数】