求TOP N 排名问题
# 177. 第N高的薪水（难度：<font color=orange>**中等**</font>）
编写一个 SQL 查询，获取 Employee 表中第 n 高的薪水（Salary）。
|Id|Salary|
|-|-|
|1|100|
|2|200|
|3|300|
例如上述 Employee 表，n = 2 时，应返回第二高的薪水 200。如果不存在第 n 高的薪水，那么查询应返回 null。
|getNthHighestSalary(2)|
|-|
|200|
**解法：**
**（1）limit+offset**
```SQL
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N := N-1;
RETURN (
# Write your MySQL query statement below.
SELECT
salary
FROM
employee
GROUP BY
salary
ORDER BY
salary DESC
LIMIT N, 1
);
END
```
**(2)子查询**
```SQL
Select distinct e.salary
From employee e
Where (select count(distinct salary) from employee where salary >e.salary) = N
```
**(3)连接查询**
```SQL
Select distinct e1.salary
From employee e1 join employee e2 on e1.salary <= e2.salary
Group by e1.salary
Having count(distinct e2.salary) = N
```
**(4)笛卡尔积**
```SQL
Select distinct e1.salary
From employee e1, employee e2 where e1.salary <= e2.salary
Group by e1.salary
Having count(distinct e2.salary) = N
```
**(5)自定义变量**
```SQL
Select distinct salary
From (select salary, @r:= if(@p=salary, @r, @r+1) as rank, @p:=salary
From employee ,(select @r:=0, @p:=NULL) init
Order by salary desc) t
Where rank = N
```
**(6)窗口函数**
```SQL
Select distinct salary
From (select salary, dense_rank() over(order by salary desc) as rank from employee)t
Where rank = N
```
**总结：单表 > 连接 （尽量不用子查询、笛卡尔积）**
# 185. 部门工资前三高的所有员工（难度：<font color=red>**困难**</font>）
**Employee** 表包含所有员工信息，每个员工有其对应的工号 **Id**，姓名 **Name**，工资 **Salary** 和部门编号 **DepartmentId** 。
<img src="https://cos.easydoc.net/17082933/files/keh2svb7.png" style="zoom: 85%;" />
编写一个 SQL 查询，找出每个部门获得前三高工资的所有员工。例如，根据上述给定的表，查询结果应返回：
<img src="https://cos.easydoc.net/17082933/files/keh2v39z.png" style="zoom: 85%;" />
**解释：**
IT 部门中，Max 获得了最高的工资，Randy 和 Joe 都拿到了第二高的工资，Will 的工资排第三。销售部门（Sales）只有两名员工，Henry 的工资最高，Sam 的工资排第二。
**（1）自变量** 650 ms
```sql
SELECT dpTable.Name AS Department, Employee, Salary
FROM
(
SELECT Name AS Employee, Salary, DepartmentId,
@rank := IF(@preDepartmentId = DepartmentId, IF(@preSalary = Salary, @rank + 0, @rank + 1), 1) AS SalaryRank,
@preDepartmentId := DepartmentId, @preSalary := Salary
FROM （SELECT * FROM Employee ORDER BY DepartmentId, Salary DESC ）, (SELECT @preDepartmentId := NULL, @preSalary := NULL, @rank := 0) AS Init
) AS RankTable
INNER JOIN Department AS dpTable ON RankTable.DepartmentId = dpTable.Id
WHERE SalaryRank <= 3;
```
**（2）子查询** 947 ms
```sql
SELECT d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
FROM Employee e1
JOIN Department d
ON e1.DepartmentId = d.Id
WHERE
3 > (SELECT COUNT(DISTINCT e2.Salary)
FROM Employee e2
WHERE e2.Salary > e1.Salary AND e1.DepartmentId = e2.DepartmentId
)
;
```
**（3）窗口函数** 563 ms
```sql
select b.name as department,a.name as employee,a.salary as salary
from employee as a , department as b,
( select id,dense_rank() over(partition by departmentid order by salary desc) as rrank from employee ) as c
where a.departmentid = b.id and a.id = c.id and c.rrank in (1,2,3)
```