StrataScratch薪资差异问题：双CASE语句相减返回NULL排查

为什么你的查询返回NULL？ #

• 你的查询是逐行计算差值：每一行只属于一个部门（要么是marketing，要么是engineering），所以两个CASE语句必然有一个返回NULL。比如marketing的行，engineering的CASE结果是NULL，NULL和数值相减结果还是NULL；engineering的行同理，最终所有行的sal_diff都是NULL。
• 没有做聚合处理：你的查询会返回CTE中的所有行，而不是只得到两个部门最高薪资的差值结果。

修正后的两种实现方案 #

方案1：用聚合函数提取各部门最高薪资后计算差值

WITH CTE AS
(
select 
    dpt.department,
    emp.salary,
    dense_rank() over (partition by department order by salary desc) as salary_rank
from db_employee as emp
join db_dept as dpt
on emp.department_id=dpt.id
where department in ('engineering', 'marketing')
)
select
    MAX(case when department = 'marketing' AND salary_rank = 1 THEN salary END) -
    MAX(case when department = 'engineering' AND salary_rank = 1 THEN salary END) as sal_diff
FROM CTE

MAX()函数会自动忽略NULL值，分别提取出marketing和engineering部门的最高薪资（salary_rank=1的薪资），再做减法就能得到正确的差值。

方案2：先筛选最高薪资行，再计算差值

WITH CTE AS
(
select 
    dpt.department,
    emp.salary,
    dense_rank() over (partition by department order by salary desc) as salary_rank
from db_employee as emp
join db_dept as dpt
on emp.department_id=dpt.id
where department in ('engineering', 'marketing')
),
top_salaries AS (
    SELECT department, salary
    FROM CTE
    WHERE salary_rank = 1
)
SELECT 
    (SELECT salary FROM top_salaries WHERE department = 'marketing') -
    (SELECT salary FROM top_salaries WHERE department = 'engineering') AS sal_diff

先通过CTE筛选出两个部门的最高薪资行，再用子查询分别取出对应薪资做差值计算。

补充说明 #

如果部门存在多个并列最高薪资（即dense_rank=1有多行），两种方案都能正常工作：MAX()会取相同的最高薪资值，子查询也会返回正确的结果（因为并列最高薪资数值一致）。

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