Employee 表,员工所有信息,如下:
+----+-------+--------+--------------+
| Id | Name | Salary | Department |
+----+-------+--------+--------------+
| 1 | 张三 | 70000 | A |
| 2 | 李四 | 80000 | B |
| 3 | 王五 | 60000 | B |
| 4 | 赵六 | 90000 | A |
| 5 | 田七 | 69000 | B |
| 6 | 王八 | 85000 | C |
| 1 | 张三Q | 70000 | A |
| 2 | 李四Q | 80000 | B |
| 3 | 王五Q | 60000 | B |
| 4 | 赵六Q | 90000 | C |
| 5 | 田七Q | 69000 | A |
| 6 | 王八Q | 85000 | A |
+----+-------+--------+--------------+
编写SQL 查询,找出每个部门工资前三高的员工。
1,第一种办法,原始SQL,这里就需要仔细思考一下,确实有点绕。
-
首先我们把Employee 表复制一份,这时我们就有两张Employee 表。分别记为 e1和e2
-
然后看需求是求同一个部门的里,工资前三的员工,所以前提条件是:
e1.Department = e2.Department 。 -
我们让e2中的Salary 大于e1中的Salary ,比如:
假设e1.Salary =e2.Salary =[1,2,3,4,5],则子查询的过程如下:
1、e1.Salary=1;则e2.Salary可以取2、3、4、5;COUNT(DISTINCT e2.Salary)=4
2、e1.Salary=2;则e2.Salary可以取3、4、5;COUNT(DISTINCT e2.Salary)=3
3、e1.Salary=3;则e2.Salary可以取4、5;COUNT(DISTINCT e2.Salary)=2
4、e1.Salary=4;则e2.Salary可以取5;COUNT(DISTINCT e2.Salary)=1
5、e1.Salary=5;则e2.Salary无法取值;COUNT(DISTINCT e2.Salary)=0
则要令COUNT(DISTINCT e2.Salary) < 3 的情况有上述的3、4、5.
这里应该就明白了吧,如果要去公司前n名员工的信息,只要让令COUNT(DISTINCT e2.Salary) < n就可以了。
现在根据上面的分析,我们完成SQL如下:
select e1.name, e1.salary, e1.department
from employee e1
where (
select count(distinct e2.salary)
from employee e2
where e2.salary > e1.salary and e1.department = e2.department
) < 3 --前几名就小于多少
order by e1.department, e1.salary desc
2,利用窗口函数row_number,SQL如下:
select name,salary,department from
(select name,salary,department,row_number() over (partition by department order by salary desc) as salary_order
from employee ) where salary_order <= 3 ;