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

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

员工表：




部门表:



最终SQL如下：

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

查询结果如下：



分析：

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 

的查询结果为：



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

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 

查询结果如下：



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

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

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

查询结果如下：



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

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

查询结果为：



故最后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;

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

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