SQL问题求助：计算营销与工程部门最高薪资绝对差额

你的SQL语句核心问题在于CASE分支里的ELSE 0设置：当目标部门（比如工程部门）没有员工时，该分支会返回0，导致MAX()将0错误认定为该部门的最高薪资，而非代表无数据的NULL，最终造成差值计算偏差。

修正写法一：调整CASE分支逻辑 #

将ELSE 0改为ELSE NULL，MAX()函数会自动忽略NULL值，确保只有目标部门的薪资参与最大值计算：

SELECT
  ABS(MAX(CASE 
           WHEN dept.department = 'marketing' THEN emp.salary 
           ELSE NULL 
         END) - 
      MAX(CASE 
           WHEN dept.department = 'engineering' THEN emp.salary 
           ELSE NULL 
         END)) AS salary_difference 
FROM db_employee emp 
JOIN db_dept dept ON emp.department_id = dept.id

更严谨写法二：先聚合部门最高薪资 #

先分组计算目标部门的最高薪资，再基于聚合结果计算差值，逻辑更清晰，也更容易处理部门无员工的场景：

WITH dept_max_salary AS (
    SELECT
        dept.department,
        MAX(emp.salary) AS max_sal
    FROM db_employee emp
    JOIN db_dept dept ON emp.department_id = dept.id
    WHERE dept.department IN ('marketing', 'engineering')
    GROUP BY dept.department
)
SELECT
    ABS(MAX(CASE WHEN department = 'marketing' THEN max_sal END) - 
        MAX(CASE WHEN department = 'engineering' THEN max_sal END)) AS salary_difference
FROM dept_max_salary

如果需要将无员工部门的薪资默认设为0，可以用COALESCE()处理NULL值：

WITH dept_max_salary AS (
    SELECT
        dept.department,
        MAX(emp.salary) AS max_sal
    FROM db_employee emp
    JOIN db_dept dept ON emp.department_id = dept.id
    WHERE dept.department IN ('marketing', 'engineering')
    GROUP BY dept.department
)
SELECT
    ABS(COALESCE(MAX(CASE WHEN department = 'marketing' THEN max_sal END), 0) - 
        COALESCE(MAX(CASE WHEN department = 'engineering' THEN max_sal END), 0)) AS salary_difference
FROM dept_max_salary

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