LeetCode MySQL-176. Second Highest Salary

    AllenLi
    LeetCode MySQL

    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.

    寫一個 SQL Query來報告第二高的薪水在 Employee 的 table。
如果沒有第二高的薪水，則返回 NULL 。
任意排序結果。

    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.

    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                |
+---------------------+

    Solution:
    1. 只有一個標題 SecondHighestSalary
    2. 選擇沒有重複的 Salary，且 Employee 表格需降冪排序。
    3. 從第一個之後取一個(也就是第二高的)

    Code:

    SELECT 
	(SELECT DISTINCT Salary
		FROM Employees 
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1
	) AS SecondhighestSalary;

    newEmployee

    Related Posts