Mysql_案例2：查询出哪些员工的薪资在部门平均薪资之上

案例：查询出哪些员工的薪资在部门平均薪资之上

1、背景：当前数据库有employee表和department表，数据分别如下：

  employee表：

 department表：

 SQL 语句：

SELECT tt.*,d.name as department_name
FROM
        (
        SELECT e.*,t.avgsal
        from 
                (
                SELECT departmentid,AVG(salary) as avgsal
                from employee
                GROUP BY departmentid ) t

        INNER JOIN
                            employee as e
        on 
                            t.departmentid=e.departmentid
        WHERE salary > t.avgsal) tt
INNER JOIN
                    department as d
on 
                tt.departmentid = d.id;

图例解释：