派生表和 WITH：把前一步的统计结果当成表，两种写法差在哪

上一篇里，括号中的查询只返回一个值：(SELECT AVG(amount) FROM orders) 算出的 127.66。这一篇把括号放宽一些：括号里也可以返回一整张表，几行几列都行，随后在外面继续查询它。

要回答的问题也随之变了：哪些城市的订单总额，高于各城市总额的平均水平？ 这里的“平均水平”不是对原始订单求平均，而是先把订单按城市汇总成几个数，再对这几个数求平均。两步，一步做不完。

orders 表还是那 9 行：

先手算，后面好核对。按城市汇总：杭州 129.00 + 320.00 + 45.00 = 494.00；上海 58.50 + 210.00 + 91.50 = 360.00；北京 199.00；city 为空的两笔 76.00 + 19.90 = 95.90。GROUP BY 把 NULL 也算作一组，所以共四组，合计 1148.90，四组平均是 $1148.90 \div 4 = 287.225$。高于这条线的只有杭州和上海。

卡住的地方：聚合函数不能套聚合函数

直觉写法是让外层直接对上一步的结果再算一次平均：

1SELECT city, SUM(amount)
2FROM orders
3GROUP BY city
4HAVING SUM(amount) > AVG(SUM(amount));

报错 ERROR 1111 (HY000): Invalid use of group function。原因跟上一篇的 WHERE amount > AVG(amount) 是同一件事的两种表现：一条查询的 SELECT 和 HAVING 只做 一轮 聚合，把多行压成一行。SUM(amount) 的结果要等这一轮算完才存在，而 AVG(SUM(amount)) 要求在同一层对这份结果立刻再做一轮。同一层没有这个位置。

办法是 另开一条查询，让它对上一步的成品再算一次。这条新查询放在哪里？放在括号里，当作一张表塞进 FROM。

写法一：把一段查询放进 FROM，当表用

1SELECT city, city_total
2FROM (
3  SELECT city, SUM(amount) AS city_total
4  FROM orders
5  GROUP BY city
6) AS city_totals
7WHERE city_total > 300;

括号里这段先独立跑一遍，产出四行两列：city 和 city_total。外层把它当成一张名为 city_totals 的表逐行判断 city_total > 300，留下杭州 494.00、上海 360.00。

这种写在 FROM 里的子查询叫 派生表。两个规矩必须记住：

第一，必须给别名。 那个 AS city_totals 不是装饰。FROM 里每张表都得有名字，真表的名字来自建表，派生表没有，只能你起；漏了会报 ERROR 1248 (42000): Every derived table must have its own alias。

第二，外层只能使用括号里 SELECT 列出的列。 所以 AS city_total 这个列名很关键：有了它，外层才能直接写 WHERE city_total > 300，而不用重复 SUM(amount) 那串东西。

换成“高于城市平均”，派生表就不够用了

上面的写法能筛固定阈值 300。但这一篇的问题是“高于城市总额的平均值”，而那个平均值得从 city_totals 这张表自己算出来。于是同一份 city_totals，一次作为“被比较的数据”，一次作为“算出平均值的来源”，需要用两遍。

派生表只能用一遍，只能把整段查询抄两次：

1SELECT city, city_total
2FROM (SELECT city, SUM(amount) AS city_total FROM orders GROUP BY city) AS a
3WHERE city_total > (
4  SELECT AVG(city_total)
5  FROM (SELECT city, SUM(amount) AS city_total FROM orders GROUP BY city) AS b
6);

这条语句能跑，结果也对（杭州、上海）。但它已经不太可读了：同一段逻辑出现两次，将来要加一个过滤条件，就得改两处，漏一处，两个数字就对不上。

写法二：用 WITH 在开头给中间结果起名字

1WITH city_totals AS (
2  SELECT city, SUM(amount) AS city_total
3  FROM orders
4  GROUP BY city
5)
6SELECT city, city_total
7FROM city_totals
8WHERE city_total > (SELECT AVG(city_total) FROM city_totals);

WITH 名字 AS (一段查询) 就是在给这段查询起名，起好之后，紧跟其后的那条语句里，city_totals 可以像真表一样出现在 FROM 或括号里的子查询中。这种带名字的中间结果叫 CTE（common table expression，公用表表达式），也叫“WITH 子句”。

逐句对一下：前半段定义“城市总额”这件事；主查询先拿它当表，在 WHERE 里又用它算平均。同一份定义被引用两次，于是刚才“抄两遍”的问题消失了。引用的仍是同一份结果，平均还是 287.225，结果只有杭州 494.00、上海 360.00 两行。以后要给中间结果加过滤条件（比如排除 city IS NULL），也只改 WITH 里那一处。

两者的真实差别在哪

先说清一件容易夸大的事：这两种写法算出来的数完全一样。CTE 不是新算法，只是把“这段查询叫什么、放在哪”换了个安排。

第二条差别最容易被忽略。中间结果只需要用一次时，两者是等价选项，这时派生表反而更短，就摆在被使用的位置旁边。分界出现在两种情形：这份中间结果要被用两次以上（派生表只能复制粘贴，逻辑一改就得多处跟着改）；或者问题本身有三步以上（WITH a AS (...), b AS (...) SELECT ... 可以排成一条流水线，b 直接引用 a）。

为什么 WITH 更容易逐段验证

SQL 最容易出错的环节往往不是语法，而是中间某一步悄悄算错了、最后结果却仍然像回事。所以要养成习惯：每一步单独跑一遍，核对行数和量级，再接下一步。

WITH 在这件事上有实际优势：那段定义以 AS ( ... ) 的形式独立存在，括号里就是一个完整的 SELECT。想验证城市总额对不对，把它单独贴出来跑一次，看到四行、杭州 494.00，就能放心往下接。嵌套写法里，中间那段藏在 FROM 内部，要拿出来得先确认外层别处没引用它、还得临时补个别名——多出的每一步都在劝你“算了，直接跑整条吧”。

名字本身也有用：结果不对时，你能分清是 city_totals 这一步就错了，还是它对了、后面筛错了。

几条别踩的线

WITH 只活在它所在的那一条语句里。 它不是视图也不是临时表：不能跨查询复用，数据库也不会为它建索引，用完就没了。

名字在同一段 WITH 里不能重复，而且只能引用前面定义过的。 a 里不能引用后面才定义的 b。

MySQL 要 8.0 及以上才有 WITH。 5.7 会直接报语法错，只能退回派生表写法。PostgreSQL、SQL Server、SQLite 也都有这个语法。

判断标准很短：中间结果只用一次，就放原地当派生表；要用两次、要串三步、或者你想一段一段核对，就提到开头写成 WITH。