题目
#176 Second Highest Salary
解题思路
依然是 Ranking 类问题。可以想到这么一个方法，对 DISTINCT Salary 进行排序加上 LIMIT 即可得到排名第 2 的行。但是题目要求若没有第 2 名的行，需要返回 NULL，而这个答案是不返回任何行，所以还需要加工。
1
2
3
4
5
6
| SELECT DISTINCT
Salary
FROM
Employee
ORDER BY Salary
LIMIT 1 , 1
|
第二步，我们多加一层 SELECT 并添加一个 IF 条件判断。如果结果有 0 行则返回 NULL，有 1 行返回正常结果。由于可以预期上一步结果只有一个，所以这里可以用 COUNT 而不用 GROUP BY。
构造测试数据
1
2
3
4
5
6
7
8
9
10
11
12
| 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);
|
预期结果：
1
2
3
4
5
| +---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+
|
一种答案
完整答案
1
2
3
4
5
6
7
8
9
10
|
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：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
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 结果，就不用在进一步加工结果。
1
2
3
4
5
6
7
8
9
10
|
SELECT
MAX(Salary)
FROM
Employee
WHERE
Salary < (SELECT
MAX(Salary)
FROM
Employee)
|
1
2
3
4
5
6
7
8
9
10
|
SELECT
MAX(Salary)
FROM
Employee
WHERE
Salary NOT IN (SELECT
MAX(Salary)
FROM
Employee)
|
本博客微信公众号
