【数据库】取前三

题设

有两表如下：
Employee 表：记录员工的 id，姓名，工资和部门id

+----+------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+------+--------+--------------+
|  1 | 赵   |  85900 |            1 |
|  2 | 钱   |  85900 |            2 |
|  3 | 孙   |  90000 |            2 |
|  4 | 李   |  89000 |            1 |
|  5 | 周   |   6767 |            1 |
|  6 | 吴   |   1234 |            1 |
|  7 | 郑   |   3456 |            1 |
|  8 | 王   |  89000 |            1 |
+----+------+--------+--------------+

Department 表：记录部门id 和对应部门名称

+----+-------+
| Id | Name  |
+----+-------+
|  1 | 部门1 |
|  2 | 部门2 |
+----+-------+

要求：
取各部门工资前三的员工显示
下分两种情况：

1. 工资排名前三
   即日常中常见的排名规则，例 x 工资 100，y 和 z 工资 200，m 和 n 工资 300，则前三为 m，n，y，z。
2. 工资前三高
   只按工资高低排序，同一阶级工资的人数不影响后面的排名，例 x 工资 100，y 和 z 工资 200，m 和 n 工资 300，则第一名为 m 和 n，第二名为 y 和 z，第三名为 x，前三为 m，n，y，z，x。

工资排名前三

思路1：
某人工资排名前三，等价于工资比其高的人小于三个
sql 语句：

select d.Name as Department,
    e.Name as Employee, 
    e.Salary
from Employee e
join Department d on e.DepartmentId = d.Id
where e.Id in(
    select e1.id from Employee e1 
	left join Employee e2
    on e1.DepartmentId = e2.DepartmentId and e1.Salary < e2.Salary
    group by e1.id
    having count(e1.id) < 3
)
order by d.Name, Salary desc;

简化：

select d.Name as Department,
    e.Name as Employee, 
    e.Salary
from Employee e
join Department d on e.DepartmentId = d.Id
where 3 > (
    select count(e1.Id) from Employee e1 
    where e1.DepartmentId = e.DepartmentId and e1.Salary > e.Salary
)
order by d.Name, Salary desc;

思路2：
某人工资排名前三，等价于比其工资的工资（包括重复）小于三个
sql 语句：

select d.Name as Department,
    e.Name as Employee, 
    e.Salary
from Employee e
join Department d on e.DepartmentId = d.Id
where 3 > (
	select count(e1.Salary)
    from Employee e1
    where e1.DepartmentID = e.DepartmentId and e1.Salary > e.Salary
)
order by d.Name, Salary desc;

运行结果：

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| 部门1      | 李       |  89000 |
| 部门1      | 王       |  89000 |
| 部门1      | 赵       |  85900 |
| 部门2      | 孙       |  90000 |
| 部门2      | 钱       |  85900 |
+------------+----------+--------+

工资前三高

某人工资前三高，等价于比其工资数额高的工资数额小于三个
sql 语句：

select d.Name as Department,
    e.Name as Employee, 
    e.Salary
from Employee e
join Department d on e.DepartmentId = d.Id
where 3 > (
	select count(distinct e1.Salary)
    from Employee e1
    where e1.DepartmentID = e.DepartmentId and e1.Salary > e.Salary
)
order by d.Name, Salary desc;

也可写为：

select d.Name as Department,
    e.Name as Employee, 
    e.Salary
from Employee e
join Department d on e.DepartmentId = d.Id
where e.Id in(
    select e1.id from Employee e1 
	left join Employee e2
    on e1.DepartmentId = e2.DepartmentId and e1.Salary < e2.Salary
    group by e1.id
    having count(distinct e2.Salary) < 3
)
order by e.DepartmentId, e.Salary DESC;

运行结果：

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| 部门1      | 李       |  89000 |
| 部门1      | 王       |  89000 |
| 部门1      | 赵       |  85900 |
| 部门1      | 周       |   6767 |
| 部门2      | 孙       |  90000 |
| 部门2      | 钱       |  85900 |
+------------+----------+--------+

题外话

MySQL 中sql语句的执行顺序：

1. from
2. on
3. join
4. where
5. group by
6. cube | rollup
7. having
8. select
9. distinct
10. order by
11. limit