问题:
Write a SQL query to get the second highest salary from the Employee
table.
+----+--------+ | Id | Salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+
For example, given the above Employee table, the query should return 200
as the second highest salary. If there is no second highest salary, then the query should return null
.
+---------------------+ | SecondHighestSalary | +---------------------+ | 200 | +---------------------+
解决:
① 先来看一种使用Limit和Offset两个关键字的解法,MySQL中Limit后面的数字限制了我们返回数据的个数,Offset是偏移量,那么如果我们想找第二高薪水,我们首先可以先对薪水进行降序排列,然后我们将Offset设为1,那么就是从第二个开始,也就是第二高薪水,然后我们将Limit设为1,就是只取出第二高薪水,如果将Limit设为2,那么就将第二高和第三高薪水都取出来。1060 ms
SELECT Salary AS SecondHighestSalary FROM Employee
GROUP BY SecondHighestSalary
UNION ALL(SELECT NULL AS SecondHighestSalary)
ORDER BY SecondHighestSalary DESC
LIMIT 1
OFFSET 1;
/*使用UNION ALL保证如果第二大的工资不存在,就返回null
LIMIT表示取返回结果的一个值
OFFSET表示从何处开始取
ORDER BY不能用Salary作为条件,因为此时使用SecondHighestSalary标识*/
【注】AS 子句可用来更改结果集列的名称或为派生列分配名称。使用GROUP BY保证工资值不存在重复,所以也可以使用DISTINCT排除重复的值。
SELECT DISTINCT Salary SecondHighestSalary
FROM Employee
UNION ALL (SELECT NULL AS SecondHighestSalary)
ORDER BY SecondHighestSalary DESC
LIMIT 1
OFFSET 1;
② 可以使用Max函数返回最大值,逻辑是我们取出的不包含最大值的数字中的最大值,即为第二大值。 1384 ms
SELECT MAX(Salary) As SecondHighestSalary FROM Employee
WHERE Salary NOT IN
(SELECT MAX(Salary) FROM Employee);
③ 就是用小于号<代替了Not in关键字,效果相同。914 ms
SELECT MAX(Salary) As SecondHighestSalary FROM Employee
WHERE Salary <
(SELECT MAX(Salary) FROM Employee);
④ 可以扩展到找到第N高的薪水的方法,只要将下面语句中的1改为N-1即可,第二高的薪水带入N-1就是1,下面语句的逻辑是,假如我们要找第二高的薪水,那么我们允许其中一个最大值存在,然后在其余的数字中找出最大的,即为整个的第二大的值;1421 ms
SELECT MAX(Salary) As SecondHighestSalary FROM Employee E1
WHERE 1 =
(SELECT COUNT(DISTINCT(E2.Salary)) FROM Employee E2
WHERE E2.Salary > E1.Salary);