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

佚名 6年前 (2019-04-09) 随笔 697人围观抢沙发百度已收录

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

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

SRE实战互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。

employee表：

department表：

SQL 语句：

 1 SELECT tt.*,d.name as department_name
 2 FROM
 3         (
 4         SELECT e.*,t.avgsal
 5         from 
 6                 (
 7                 SELECT departmentid,AVG(salary) as avgsal
 8                 from employee
 9                 GROUP BY departmentid ) t
10 
11         INNER JOIN
12                             employee as e
13         on 
14                             t.departmentid=e.departmentid
15         WHERE salary > t.avgsal) tt
16 INNER JOIN
17                     department as d
18 on 
19                 tt.departmentid = d.id;