mysql查找刘林所在部门名称_数据库笔试——查出各部门超出部门平均薪资的员工的姓名，薪资，所在部门名称及部门平均薪水...

有一段时间没做数据库的题了，前面面试偶然做到这么一题目，觉得不错，凭记忆将题目记下来，然后在数据库中实现了一遍。

题目大概是这样: 一张员工表 employee,包含字段 id,name,salary,dep_no; 一张部门信息表 department，包含字段 id,dep_no,name，其中 employee 的 dep_no 关联 department 的 dep_no;写 sql 查出各部门超出部门平均薪资的员工的姓名，薪资，所在部门名称及部门平均薪水。

下面是我创建的两张表:

DROP TABLE IF EXISTSdepartment;CREATE TABLEdepartment (

idint NOT NULL auto_increment PRIMARY KEY,

dep_noint NOT NULL,

`name`VARCHAR(50) NOT NULL);INSERT INTO department(dep_no,name) VALUES(10,'研发部');INSERT INTO department(dep_no,name) VALUES(20,'测试部');INSERT INTO department(dep_no,name) VALUES(30,'财务部');

DROP TABLE IF EXISTSemployee;CREATE TABLEemployee (

`id`int NOT NULLauto_increment,

`name`varchar(50) NOT NULL,

`salary`double(10,2) NOT NULL DEFAULT 0,

`dep_no`INTEGER NOT NULL,PRIMARY KEY(`id`)

);INSERT INTO employee(name,salary,dep_no) SELECT 'Bruce',15000.00,dep_no FROM department WHERE `name` = '研发部';INSERT INTO employee(name,salary,dep_no) SELECT 'Kevin',16000.00,dep_no FROM department WHERE `name` = '研发部';INSERT INTO employee(name,salary,dep_no) SELECT 'Lww',12000.00,dep_no FROM department WHERE `name` = '财务部';INSERT INTO employee(name,salary,dep_no) SELECT 'Linda',10000.00,dep_no FROM department WHERE `name` = '财务部';INSERT INTO employee(name,salary,dep_no) SELECT 'David',10000.00,dep_no FROM department WHERE name = '测试部';INSERT INTO employee(name,salary,dep_no) SELECT 'Sandy',8000.00,dep_no FROM department WHERE name = '测试部';INSERT INTO employee(name,dep_no) SELECT 'Dennis' ,dep_no FROM department WHERE name = '测试部';

题目的分析：首先要得到目标员工的姓名，薪资不用说要从 employee 表中获得；至于后两个数据部门名称及部门平均薪资，直接查 department 肯定得不到，需要两表联立查询，不妨就写出这块的 sql :

SELECT AVG(e.salary) ASavg_sal,d.dep_no,d.`name`FROMemployee e,department dWHERE d.dep_no =e.dep_noGROUP BY d.dep_no,d.`name`;

这里之所以 dep_no,name两个字段作为分组字段，是因为后面的查询目标中有 name，而 dep_no 作为部门的一个唯一标识，它是天然的分组字段，同时考虑到这一部中查询结果要与 employee 表联立，而两者之又只能以 dep_no 连接，而一般情况下不会出现一个部门对应多个部门号的情况，所以 group by dep_no 与 group by dep_no,name 正常情况下结果应该是一样的。

将上面的结果作为临时表 tmp 放入主查询，最终得到：

SELECT e.`name`,e.salary,tmp.dep_name,tmp.avg_sal FROMemployee e,(SELECT AVG(e.salary) AS avg_sal,d.dep_no,d.`name` ASdep_nameFROMemployee e,department dWHERE d.dep_no =e.dep_noGROUP BYd.dep_no,d.`name`

)tmpWHERE e.dep_no =tmp.dep_noAND e.salary > tmp.avg_sal;

当然，如果真的存在一个 name 对应多个 dep_no 或者说存在不同部门取同一个名称的情况 ，那么也可用下面的方式得到正确结果：

SELECT e.`name`,e.salary,d.`name` AS dep_name,tmp.avg_sal FROM employee e,department d,(

SELECT AVG(e.salary) AS avg_sal,d.dep_no

FROM employee e,department d

WHERE d.dep_no=e.dep_no

GROUP BY d.dep_no

)tmp WHERE e.dep_no= tmp.dep_no AND e.dep_no =d.dep_no

AND e.salary> tmp.avg_sal;

还是将 dep_no 作为唯一分组字段，外层查询再关联 department 就行了。

PS: 搜了一圈，好像其他地方也有类似这样的题目，但我觉得我做的这一版还是很细致的，很有参考价值。