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

LeetCode Solution, Medium, 176. Second Highest Salary

Comments

LeetCode Solution

More from this blog

如何開始入門軟體工程領域 - 名詞解釋（長期更新）

我的 MacBook Pro (Apple Silicon) 設定

ChatGPT 下的發展預想

一些覺得有用的工具蒐藏

LeetCode Solution, Medium, 607. Sales Person

176. Second Highest Salary

題目敘述

題目翻譯

解法解析

程式範例

SQL

Command Palette

Comments

LeetCode Solution

More from this blog

176. Second Highest Salary

題目敘述

題目翻譯

解法解析

程式範例

SQL