心得:
找出該資料表內第二高薪水的筆數,若無則回傳null。
問題:
Write a SQL query to get the second highest salary from the
Employee
table.
1234567 +----+--------+| 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 returnnull
.
答案:
- Sub Query
12345# Write your MySQL query statement belowSELECT MAX(a.Salary) AS SecondHighestSalaryFROM Employee AS aWHERE a.Salary < (SELECT MAX(z.Salary)FROM Employee AS z) DISTINCT
> 不重複
12345678# Write your MySQL query statement belowSELECT(SELECT DISTINCT SalaryFROM EmployeeORDER BY Salary DESCLIMIT 1, 1) AS SecondHighestSalaryGROUP BY
> 不重複
123456789# Write your MySQL query statement belowSELECT(SELECT SalaryFROM EmployeeGROUP BY SalaryORDER BY Salary DESCLIMIT 1, 1) AS SecondHighestSalary