8/13 每日一題(MYSQL 選擇第二高薪水, 若不存在則回存null)

Table: Employee

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

 

Write a solution to find the second highest salary from the Employee table. If there is no second highest salary, return null (return None in Pandas).

The result format is in the following example.

 

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                | 

+---------------------+ 

#選擇 第二高的值,如果沒有就是null

SELECT IFNULL(

    (SELECT salary

    FROM Employee

    ORDER BY salary DESC

    LIMIT 1 OFFSET 1),null

) AS  SecondHighestSalary

---------------參考解答

select
(select distinct Salary 
from Employee order by salary desc 
limit 1 offset 1) 
as SecondHighestSalary;

-----------------------參考解答

select max(salary) as SecondHighestSalary
from employee
where salary<>(select max(salary) from employee);