选出每个部门薪资前三的员工的信息

题目：现在有员工表和部门表，选出每个部门中薪资前三的员工的具体信息。具体表结构如下：

员工表：

[img]http://dl2.iteye.com/upload/attachment/0105/7406/bfec5fba-44ca-3718-944d-babe625f5813.png[/img]


部门表:

[img]http://dl2.iteye.com/upload/attachment/0105/7408/7ca9afee-5f2e-3805-959b-28f49c083d72.png[/img]

最终SQL如下：

GROUP BY 
	dep.id ,emp.id,emp.name ,emp.salary,dep.name
HAVING COUNT(*)<=3
ORDER BY 
	emp.departmentId , emp.salary ;

查询结果如下：

[img]http://dl2.iteye.com/upload/attachment/0105/7412/1734b97b-fc5f-3721-bae5-312c438237cc.png[/img]

分析：

SELECT 
	emp.id , emp.name ,emp.salary,  dep.id as depId , dep.name as depName
FROM 
	tb_employee emp ,tb_employee emp1, tb_department dep
WHERE
	emp.salary <=emp1.salary 
	AND emp.departmentId = emp1.departmentId
	AND emp.departmentId = dep.id 

的查询结果为：

[img]http://dl2.iteye.com/upload/attachment/0105/7414/a5a8088e-496e-3b55-b97d-a84c32174374.png[/img]

这个很难看出甚麽来，我们在查询结果中添加两列：

SELECT 
	emp.id , emp.name ,emp.salary,  emp1.name  as  name1, emp1.salary as salary1, dep.id as depId , dep.name as depName
FROM 
	tb_employee emp ,tb_employee emp1, tb_department dep
WHERE
	emp.salary <=emp1.salary 
	AND emp.departmentId = emp1.departmentId
	AND emp.departmentId = dep.id 

查询结果如下：

[img]http://dl2.iteye.com/upload/attachment/0105/7416/42fcf3f4-acdf-3a68-86ea-2e8872415972.png[/img]

可以看出，这其实就是一个自连接查询。

由于要找出每个部门中薪资最高的员工的信息，故要进行分组操作。

SELECT 
	emp.id , emp.name ,emp.salary, dep.id as depId , dep.name as depName
FROM 
	tb_employee emp ,tb_employee emp1, tb_department dep
WHERE
	emp.salary <=emp1.salary 
	AND emp.departmentId = emp1.departmentId
	AND emp.departmentId = dep.id 
GROUP BY 
	dep.id ,emp.id,emp.name ,emp.salary,dep.name

查询结果如下：

[img]http://dl2.iteye.com/upload/attachment/0105/7423/685a56e4-cec2-307d-bc22-663d1fc57e02.png[/img]

这里需要注意一点：group by 有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面。

添加HAVING COUNT(*)<=3，表示，在指定的一个部门中，工资大于或者等于该员工的工资的人数不大于3,即该员工工资处于该部门的前三的最后一名(如果该部门只有两名员工，则为第二名，如果只有一名员工，则为第一名).

查询结果为：

[img]http://dl2.iteye.com/upload/attachment/0105/7425/57e7b9e9-4f99-3bd0-a7f0-24ccbfad445a.png[/img]

故最后SQL语句为：

SELECT 
	emp.id , emp.name ,emp.salary, dep.id as depId , dep.name as depName
FROM 
	tb_employee emp ,tb_employee emp1, tb_department dep
WHERE
	emp.salary <=emp1.salary 
	AND emp.departmentId = emp1.departmentId
	AND emp.departmentId = dep.id 
GROUP BY 
	dep.id ,emp.id,emp.name ,emp.salary,dep.name
HAVING COUNT(*)<=3
ORDER BY 
	emp.departmentId , emp.salary desc;

注：该题目来自于[url]https://oj.leetcode.com/problems/department-top-three-salaries/[/url]

答案参考于[url]http://www1.huachu.com.cn/read/readbookinfo.asp?sectionid=1000006875[/url]