[MySQL][LeetCode][Easy] 176. Second Highest Salary

心得:

找出該資料表內第二高薪水的筆數,若無則回傳null。

問題:

Write a SQL query to get the second highest salary from the Employee table.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

+----+--------+
>
> | Id | Salary |
>
> +----+--------+
>
> | 1 | 100 |
>
> | 2 | 200 |
>
> | 3 | 300 |
>
> +----+--------+
>
>

For example, given the above Employee table, the second highest salary is 200. If there is no second highest salary, then the query should return null.
答案:

  1. Sub Query
1
2
3
4
5
6

# Write your MySQL query statement below
SELECT MAX(a.Salary) AS SecondHighestSalary
FROM Employee AS a
WHERE a.Salary < (SELECT MAX(z.Salary)
FROM Employee AS z)
  1. DISTINCT > 不重複
1
2
3
4
5
6
7
8
9

# Write your MySQL query statement below
SELECT
(
SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1, 1
) AS SecondHighestSalary
  1. GROUP BY > 不重複
1
2
3
4
5
6
7
8
9
10

# Write your MySQL query statement below
SELECT
(
SELECT Salary
FROM Employee
GROUP BY Salary
ORDER BY Salary DESC
LIMIT 1, 1
) AS SecondHighestSalary