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

-- 逻辑拆解：查找绩效得分高于部门平均分的员工，那么要先把部门员工平均分找出来，再去比对
With avg_performance AS(
    SELECT t2.department_name,AVG(t1.performance_score) performance_score
    FROM employee_projects t1
    LEFT JOIN department_info t2
    ON t1.employee_id = t2.employee_id
    GROUP BY t2.department_name
),
table_2 AS(
    SELECT t1.employee_id,t2.department_name,t1.performance_score
    FROM employee_projects t1
    LEFT JOIN department_info t2
    ON t1.employee_id = t2.employee_id
)

SELECT t1.employee_id,t1.department_name,t1.performance_score
FROM table_2 t1
INNER JOIN avg_performance t2
ON t1.department_name = t2.department_name
WHERE t1.performance_score > t2.performance_score