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

2021年09月15日 阅读数:1
这篇文章主要向大家介绍Mysql_案例2:查询出哪些员工的薪资在部门平均薪资之上,主要内容包括基础应用、实用技巧、原理机制等方面,希望对大家有所帮助。

案例:查询出哪些员工的薪资在部门平均薪资之上数据库

一、背景:当前数据库有employee表和department表,数据分别以下:spa

  employee表:3d

 

 department表:code

 SQL 语句:blog

 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;

图例解释:class