176. Second Highest Salary

題目敘述

SQL Schema >

Create table If Not Exists Employee (id int, salary int)
Truncate table Employee
insert into Employee (id, salary) values ('1', '100')
insert into Employee (id, salary) values ('2', '200')
insert into Employee (id, salary) values ('3', '300')

Table: Employee

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| salary      | int  |
+-------------+------+
id is the primary key column for this table.
Each row of this table contains information about the salary of an employee.

Write an SQL query to report the second highest salary from the Employee table. If there is no second highest salary, the query should report null.

Example 1:

Input:
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
Output:
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

Example 2:

Input:
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
+----+--------+
Output:
+---------------------+
| SecondHighestSalary |
+---------------------+
| null                |
+---------------------+

題目翻譯

很簡單的需求，有一個資料表 Employee，然後要從中找出 salary 第二高的資料。

解法解析

這題滿適合的就使用 LIMIT 和 OFFSET 來找出第二高的資料。雖然在討論區有一個 upvote 很高的解法。但是我覺得這樣的話彈性太低了，如果今天是要找第三、第四的話就不好拓展了。

SELECT
    MAX(Salary) AS SecondHighestSalary
FROM
    Employee
WHERE
    Salary NOT IN (
        SELECT
            MAX(Salary)
        FROM
            Employee
    )

程式範例

SQL

# Write your MySQL query statement below
SELECT
    IFNULL(
        (
            SELECT
                DISTINCT salary
            FROM
                Employee
            ORDER BY
                salary DESC
            LIMIT
                1 OFFSET 1
        ),
        NULL
    ) AS SecondHighestSalary