/

LeetCode Database Second Highest Salary 176

176. Second Highest Salary
  Write a SQL query to get the second highest salary from the Employee table.

+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+

For example, given the above Employee table, the second highest salary is 200. If there is no second highest salary, then the query should return null.

大体意思

写 SQL 查询出第二高薪水的 Id。如何没有第二高,则返回 null

自己的解法

SELECT
Salary
FROM
Employee
ORDER BY Salary DESC
LIMIT 1,1

但是在没有第二高的时候将没有返回值,不符合题意;看了别人的,发现自己也少考虑 DISTINCT

别人的解法

多加一层 SELECT 并添加一个 IF 条件判断。如果结果有 0 行则返回 NULL,有 1 行返回正常结果。由于可以预期上一步结果只有一个,所以这里可以用 COUNT 而不用 GROUP BY。

构造测试数据:

CREATE TABLE IF NOT EXISTS Employee (
Id INT,
Salary INT
);
DELETE FROM Employee;
INSERT INTO Employee VALUES
(1, 100),
(2, 200),
(3, 100),
(4, 300);

预期结果:

+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+

解法一:

SELECT 
IF(COUNT(Salary) >= 1, Salary, NULL) AS SecondHighestSalary
FROM
(SELECT DISTINCT
Salary
FROM
Employee
ORDER BY Salary DESC
LIMIT 1 , 1) tmp

解法二:
正常 Ranking 类问题解法,使用自定义变量计算排名。接着和上面一种解法类似需要对结果进行处理,没有第 2 名的返回 NULL:

SELECT 
IF(COUNT(Salary) >= 1, Salary, NULL) AS SecondHighestSalary
FROM
(SELECT DISTINCT
Salary
FROM
(SELECT
Id,
Salary,
@rank:=IF(@prevVal > Salary, @rank:=@rank + 1, @rank) AS Rank,
@prevVal:=Salary
FROM
Employee, (SELECT @prevVal:=NULL) x, (SELECT @rank:=1) y
ORDER BY Salary DESC) tmp
WHERE
tmp.Rank = 2) tmp2

解法三:
上面两种解法都是可以扩展到任意排名的,如果想偏一点可以得到其他解法。排名第 2 可以看做是除了 MAX 之外的 MAX,可以得到这两种类似的解法。由于 MAX 函数可以返回 NULL 结果,就不用在进一步加工结果。

SELECT 
MAX(Salary)
FROM
Employee
WHERE
Salary < (SELECT
MAX(Salary)
FROM
Employee)
SELECT 
MAX(Salary)
FROM
Employee
WHERE
Salary NOT IN (SELECT
MAX(Salary)
FROM
Employee)

Reference:
Leetcode Database: #176 Second Highest Salary | tsuinteru