SQL面试题：176. Second Highest Salary

https://leetcode.com/problems/second-highest-salary/description/

 

Description

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+Copy to clipboardErrorCopied

查找工资第二高的员工。

+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+Copy to clipboardErrorCopied

没有找到返回 null 而不是不返回数据。

SQL Schema

DROP TABLE
IF
    EXISTS Employee;
CREATE TABLE Employee ( Id INT, Salary INT );
INSERT INTO Employee ( Id, Salary )
VALUES
    ( 1, 100 ),
    ( 2, 200 ),
    ( 3, 300 );Copy to clipboardErrorCopied

Solution

为了在没有查找到数据时返回 null，需要在查询结果外面再套一层 SELECT。

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

首先将数据按工资降序排序，然后去重，最后使用 limit 取出第二高的工资数据。