查询第二高薪资时IFNULL函数失效，单测试用例未通过求解

问题场景 #

需求：编写SQL从Employee表中查询第二高薪资，若无第二高薪资则返回null。

使用的代码：

select ifnull(x.salary,NULL) as SecondHighestSalary
from(
    select *, 
       dense_rank() over( order by salary) as rnk
       from Employee
) x
where x.rnk =2

该代码通过了大部分测试用例，但在Employee表仅含一条薪资为100的记录时，输出为空表，而非预期的null值。不清楚IFNULL为何未生效，请求告知该测试用例失败的原因。

原因分析 #

• 当表中只有一条记录时，子查询生成的rnk值只有1，外层where x.rnk=2的过滤条件不会匹配任何行，导致外层查询返回空结果集。
• IFNULL()函数的作用是替换单个字段的NULL值，它只能作用于已经存在的行中的字段。如果整个查询没有返回任何行，IFNULL()根本没有执行的机会，自然无法生成包含NULL的结果行。

修正思路 #

要实现无第二高薪资时返回NULL，需要保证查询至少返回一行数据。可以通过以下方式调整：

1. 子查询直接嵌套处理：

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

2. 结合聚合函数处理空结果集：

select ifnull(max(x.salary), null) as SecondHighestSalary
from(
    select salary, dense_rank() over(order by salary) as rnk
    from Employee
) x
where x.rnk = 2;

聚合函数max()在没有匹配行时会返回NULL，这样就能确保查询始终返回一行结果。

内容的提问来源于stack exchange，提问作者Digant Dixit