LeetCode SQL 176、177 、184 、185

176. Second Highest Salary第N高的薪水

（1）题目

Write a SQL query to get the second highest salary from the Employee table.
编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary） 。
±—±-------+
| Id | Salary |
±—±-------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
±—±-------+
For example, given the above Employee table, the query should return 200 as the second highest salary. If there is no second highest salary, then the query should return null.

例如上述 Employee 表，SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null。
±--------------------+
| SecondHighestSalary |
±--------------------+
| 200 |
±--------------------+

（2）题解

SELECT DISTINCT Salary as 'SecondHighestSalary' FROM Employee
ORDER BY Salary DESC
LIMIT 1,1

此解法错误，因为无法解决本表可能只有一项记录。

方法1：
使用IFNULL函数

SELECT 
IFNULL(
	(SELECT DISTINCT Salary FROM Employee
	ORDER BY Salary DESC
	LIMIT 1,1)
	,NULL)
AS 'SecondHighestSalary'

其中：

IFNULL(expression_1,expression_2);

如果expression_1不为NULL，则IFNULL函数返回expression_1; 否则返回expression_2。

方法2：
将此表作为临时表，便可涵盖为空的情况。
如果这张临时表中查不到该记录就会返回空，再进行SELECT就会返回NULL。

SELECT 
	(SELECT DISTINCT Salary FROM Employee
	ORDER BY Salary DESC
	LIMIT 1,1)
AS 'SecondHighestSalary'

方法3：

SELECT 
	(
	SELECT MAX(DISTINCT Salary) FROM Employee
	-- 在小于最大的里面找最大的就是第2大的
	WHERE Salary<(SELECT MAX(DISTINCT Salary) FROM Employee)
	)
AS 'SecondHighestSalary'

177. Nth Highest Salary第N高的薪水

（1）题目

Write a SQL query to get the nth highest salary from the Employee table.
编写一个 SQL 查询，获取 Employee 表中第 n 高的薪水（Salary）。

±—±-------+
| Id | Salary |
±—±-------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
±—±-------+
For example, given the above Employee table, the nth highest salary where n = 2 is 200. If there is no nth highest salary, then the query should return null.

例如上述 Employee 表，n = 2 时，应返回第二高的薪水 200。如果不存在第 n 高的薪水，那么查询应返回 null。

±-----------------------+
| getNthHighestSalary(2) |
±-----------------------+
| 200 |
±-----------------------+

（2）题解

方法1（单表查询）：

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
	SET N := N-1;
	RETURN(
		SELECT Salary FROM Employee
		-- 用group by按薪水分组后再order by来解决同薪同名且不跳级的问题
		GROUP BY Salary
		ORDER BY Salary DESC
		LIMIT N,1
	);
END

sql中:=符号为对变量赋值
注意第1个为RETURNS，第2个为RETURN

排名第N高意味着要跳过N-1个薪水，由于无法直接用LIMIT N-1（不能使用表达式），所以需先在函数开头处理N为N=N-1。

注：这里不能直接用limit N-1是因为LIMIT和OFFSET字段后面只接受正整数（意味着0、负数、小数都不行）或者单一变量（意味着不能用表达式），也就是说想取一条，LIMIT 2-1、LIMIT 1.1这类的写法都是报错的。

方法2（窗口函数）：

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
	RETURN(
		SELECT DISTINCT Salary
		FROM(
			SELECT Salary,DENSE_RANK() OVER(ORDER BY Salary DESC) AS ranknumber
			FROM Employee
		) t
		WHERE ranknumber = N
	);
END

DENSE_RANK()可以做到相同的Salary排名相同，且排名不会产生空位

调用该函数，查询出结果：

SELECT getNthHighestSalary(输入N)

删除该函数：

DROP FUNCTION 函数名称

修改该函数：

ALTER FUNCTION 名称
-- 之后写法同CREATE

184. Department Highest Salary部门工资最高的员工

（1）题目

The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.

Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。

±—±------±-------±-------------+
| Id | Name | Salary | DepartmentId |
±—±------±-------±-------------+
| 1 | Joe | 70000 | 1 |
| 2 | Jim | 90000 | 1 |
| 3 | Henry | 80000 | 2 |
| 4 | Sam | 60000 | 2 |
| 5 | Max | 90000 | 1 |
±—±------±-------±-------------+

The Department table holds all departments of the company.
Department 表包含公司所有部门的信息。

±—±---------+
| Id | Name |
±—±---------+
| 1 | IT |
| 2 | Sales |
±—±---------+

Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, your SQL query should return the following rows (order of rows does not matter).

编写一个 SQL 查询，找出每个部门工资最高的员工。对于上述表，您的 SQL 查询应返回以下行（行的顺序无关紧要）。

±-----------±---------±-------+
| Department | Employee | Salary |
±-----------±---------±-------+
| IT | Max | 90000 |
| IT | Jim | 90000 |
| Sales | Henry | 80000 |
±-----------±---------±-------+

（2）题解

SELECT d.Name AS 'Department',e.Name AS 'Employee',e.Salary
FROM Employee AS e
-- 不能用LEFT JOIN，因为要考虑Department表为空的情况
JOIN Department AS d 
ON e.DepartmentId = d.Id
-- 2.再使用IN子句来进行筛选
WHERE (e.Salary,e.DepartmentId)
IN
(
    -- 1.先取出每个部门的最高工资对应的部门ID和薪水
    SELECT MAX(Salary),DepartmentId
    FROM Employee 
    GROUP BY DepartmentId
);

185. Department Top Three Salaries部门工资前三高的所有员工

（1）题目

Employee 表包含所有员工信息，每个员工有其对应的工号 Id，姓名 Name，工资 Salary 和部门编号 DepartmentId 。

±—±------±-------±-------------+
| Id | Name | Salary | DepartmentId |
±—±------±-------±-------------+
| 1 | Joe | 85000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
| 7 | Will | 70000 | 1 |
±—±------±-------±-------------+
Department 表包含公司所有部门的信息。

±—±---------+
| Id | Name |
±—±---------+
| 1 | IT |
| 2 | Sales |
±—±---------+
编写一个 SQL 查询，找出每个部门获得前三高工资的所有员工。例如，根据上述给定的表，查询结果应返回：

±-----------±---------±-------+
| Department | Employee | Salary |
±-----------±---------±-------+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 85000 |
| IT | Will | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
±-----------±---------±-------+
解释：

IT 部门中，Max 获得了最高的工资，Randy 和 Joe 都拿到了第二高的工资，Will 的工资排第三。销售部门（Sales）只有两名员工，Henry 的工资最高，Sam 的工资排第二。

（2）题解

方法1（子查询）：

-- 3.再JOIN表Department
SELECT d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
FROM Employee e1
JOIN Department d
ON e1.DepartmentId = d.Id
-- 2.不超过3个值比这些值大,小于3条，即取出前3高
WHERE(
-- 1.先查比e1工资高的有多少条
SELECT COUNT(DISTINCT e2.Salary) FROM Employee e2
WHERE e2.Salary > e1.Salary
AND e1.DepartmentId = e2.DepartmentId
) < 3;

举例：

当 e1 = e2 = [4,5,6,7,8]（薪水）

e1.Salary = 4，e2.Salary 可以取值 [5,6,7,8]，count(DISTINCT e2.Salary) = 4

e1.Salary = 5，e2.Salary 可以取值 [6,7,8]，count(DISTINCT e2.Salary) = 3

e1.Salary = 6，e2.Salary 可以取值 [7,8]，count(DISTINCT e2.Salary) = 2

e1.Salary = 7，e2.Salary 可以取值 [8]，count(DISTINCT e2.Salary) = 1

e1.Salary = 8，e2.Salary 可以取值 []，count(DISTINCT e2.Salary) = 0

满足条件count(DISTINCT e2.Salary) < 3时，e1.Salary 可取值为 [6,7,8]，即是集合e中前 3 高的薪水。

方法2：（窗口函数）

SELECT Department, Employee, Salary
FROM
(
SELECT d.Name AS 'Department',e.Name AS 'Employee',e.Salary AS 'Salary',
DENSE_RANK() OVER(PARTITION BY DepartmentId ORDER BY Salary DESC) AS ranknumber
-- FROM table1,table2相当于内连接
FROM Employee e,Department d
WHERE e.DepartmentId = d.Id
) t
WHERE ranknumber <=3;

DENSE_RANK()可以做到相同的Salary排名相同，且排名不会产生空位

PARTITION BY做到所有的数据分组后都进行显示

FROM table1,table2

即table1和table2做INNER JOIN