如何将带WITH子句的SQL查询改写为不使用WITH构造的版本

最终改写结果 #

SELECT dt.dept_name
FROM 
    -- 对应原WITH中的dept_total：计算每个部门的总薪资
    (
        SELECT dept_name, SUM(salary) AS total_salary
        FROM instructor
        GROUP BY dept_name
    ) AS dt,
    -- 对应原WITH中的dept_total_avg：计算所有部门总薪资的平均值
    (
        SELECT AVG(total_salary) AS avg_total
        FROM (
            SELECT SUM(salary) AS total_salary
            FROM instructor
            GROUP BY dept_name
        ) AS dept_total
    ) AS dta
WHERE dt.total_salary >= dta.avg_total;

逻辑拆解讲解 #

1. 原WITH语句的本质 #

WITH子句（也叫公共表表达式CTE）的作用是提前定义可复用的临时结果集，本质就是语法糖，把需要重复写的子查询提前命名，简化后续代码。要去掉WITH，只需要把每个CTE的定义逻辑直接替换为对应的子查询即可。
原语句的三层逻辑：

• 第一层：按部门分组计算每个部门的薪资总和，生成临时结果dept_total
• 第二层：对dept_total里的所有部门总薪资求平均值，生成临时结果dept_total_avg
• 第三层：关联两个临时结果，筛选出总薪资高于平均值的部门名称

2. 子查询替换逻辑 #

• 把原dept_total的定义直接写在FROM子句中，命名别名dt，保存每个部门的名称和总薪资
• 把原dept_total_avg的定义也写在FROM子句中，命名别名dta，这里因为计算部门总薪资的逻辑需要复用，所以再嵌套一层子查询实现和原CTE一样的效果
• 最后在WHERE子句中做大小判断即可（注：原SQL存在笔误，dept_total avg.value应为dept_total_avg.value，改写时已修正）

3. 常见误区纠正 #

你之前写的SELECT AVG(salary) GROUP BY dept_name是计算单个部门内所有员工的平均薪资，和需求里的「所有部门总薪资的平均值」是两个完全不同的统计维度：

• 前者统计粒度是「员工」，求的是单个部门里每个员工的薪资平均值
• 需求需要的统计粒度是「部门」，先求每个部门所有员工薪资总和，再对这些总和求平均值

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