一道Mysql有坑的题

题目内容

有一张名为Employee的表,表中的数据可能为:

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

要求查询出第二高Salary,若没有第二高Salary则返回null

解题思路
1. 查询第二高,即先要对查询结果进行从大到小排序,再取第二个
select Salary from Employee order by Salary desc limit 1,1  

下辈子想做头猪的博客
页面提示答案错误

2. 猜想错误原因:可能同时有两个最大值
select distinct Salary as SecondHighestSalary from Employee order by Salary desc limit 1,1  

下辈子想做头猪的博客

3.猜想错误原因:若Employee表为空,返回是不是null呢

所以在控制台测试一下,如下图所示,返回结果为Empty set 下辈子想做头猪的博客
所以使用ifnull函数

select ifnull((select distinct Salary as SecondHighestSalary from Employee order by Salary desc limit 1,1),null)  

下辈子想做头猪的博客
依然报错,在本地测试 不难发现,错误原因应该是题目要求返回查询结果的标题为SecondHighestSalary(这个要求可以从上图的爆错信息看出来),而这里是查询语句

4. 答案
select ifnull((select distinct Salary from Employee order by Salary desc limit 1,1),null) as SecondHighestSalary  

下辈子想做头猪的博客
提示答案正确

5. 答案2
SELECT MAX(Salary) AS SecondHighestSalary FROM Employee,(SELECT MAX(Salary) AS MaxS FROM Employee) AS T WHERE Salary != MaxS  

zhutougg

继续阅读此作者的更多文章