禁用子查询（允许CTE）实现含空部门补全的部门最高薪资查询

• 原查询逻辑完全不符合需求，存在5处核心错误：
  • 语法错误：UNION左侧查询后附加的as t1别名不符合SQL标准，执行会直接报错。
  • 关联条件不规范：CTE内的关联条件使用了原表名dept而非定义的别名d，容易引发逻辑歧义。
  • 全局最高薪资计算错误：top_salary CTE内关联了dept表，若emp表存在无匹配deptid的脏数据，会漏掉对应行，取到的结果不是emp表的全局最高薪资。
  • 无员工部门赋值逻辑错误：第二个UNION分支中关联top_salary的条件为ts.deptid = cte.deptid，无员工部门的cte.deptid为NULL，关联条件永远不成立，最终赋值薪资为NULL，无法获取全局最高薪资。
  • 冗余计算：CTE中定义的salary_rank字段全程未被使用，属于多余逻辑。

按照SQL标准的子查询定义（嵌套在另一查询的语法子句内部的SELECT语句），UNION前后的两个独立查询不属于子查询范畴。UNION连接的是两个同层级的顶层查询，仅用于合并结果集，不存在嵌套关系。

约束说明 #

通常“禁止使用子查询”指禁止在WHERE/FROM/JOIN等子句中直接嵌套SELECT语句，允许使用CTE。原UNION方案虽不属于子查询，但结构冗余且存在逻辑错误，可按以下方式改写：

改写后SQL #

WITH dept_max_salary AS (
  -- 计算每个有员工的部门的最高薪资
  SELECT deptid, MAX(salary) AS max_salary
  FROM emp
  GROUP BY deptid
),
global_max_salary AS (
  -- 直接从emp表取全局最高薪资，避免受dept关联影响
  SELECT MAX(salary) AS max_salary
  FROM emp
)
SELECT 
  d.deptid,
  COALESCE(dms.max_salary, gms.max_salary) AS dept_highest_salary
FROM dept d
LEFT JOIN dept_max_salary dms 
  ON d.deptid = dms.deptid
-- 全局最高薪资CTE仅返回1行，笛卡尔积不影响结果，无需额外关联条件
CROSS JOIN global_max_salary gms;

逻辑说明 #

• 以dept表为左基准，覆盖所有部门，包括无员工的部门。
• 左连接dept_max_salary后，有员工的部门直接匹配到自身最高薪资，无员工的部门该字段为NULL。
• 通过CROSS JOIN关联单行的全局最高薪资结果，再用COALESCE函数将NULL值替换为全局最高薪资，完全符合需求。
• 整个查询无嵌套子查询，所有聚合逻辑均封装在CTE中，符合约束要求。
• 若需返回部门名称，可直接在SELECT列表中添加d.deptname字段。

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