176. Second Highest Salary----leetcode----寻找第二大纪录的方法

解题思路

依然是 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

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

一种答案

完整答案

1
2
3
4
5
6
7
8
9
10

-- Runtime: 943 ms
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

-- Runtime: 962 ms
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

-- Runtime: 963 ms
SELECT 
    MAX(Salary)
FROM
    Employee
WHERE
    Salary < (SELECT 
            MAX(Salary)
        FROM
            Employee)

1
2
3
4
5
6
7
8
9
10

-- Runtime: 1219 ms
SELECT 
    MAX(Salary)
FROM
    Employee
WHERE
    Salary NOT IN (SELECT 
            MAX(Salary)
        FROM
            Employee)