问题描述:编写一个 SQL 查询,获取 Employee
表中第二高的薪水(Salary),如果不存在第二高的薪水,那么查询应返回 null
。
中间过程:
第一次:
select IFNULL((select Salary from Employee order by Salary desc limit 1,1),null) SecondHighestSalary;
错误原因:
第二次:
select ifnull((select salary from Employee order by salary asc limit 1,1),null) as SecondHighestSalary;
错误原因:
第三次:
select IFNULL((select distinct salary from Employee order by salary asc limit 1,1),null) SecondHighestSalary;
错误原因:
正确结果:
select IFNULL((select distinct Salary from Employee order by Salary desc limit 1,1),null) SecondHighestSalary;
注意事项:
select ifnull(表达式1,表达式2)其中表达式1是那个字段需要判断是null,表达式2是该字段为null后的替换值。
order by 排序字段 排序方式(desc是降序,asc是升序) limit 1,1(分页第二高)
select distinct 列名 (去重)