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

同一份中间结果要用两次时，从 FROM 里的子查询换到 WITH 到底换掉了什么，以及为什么 WITH 更容易逐段核对

> 子查询 · CTE · 取数入门 · 约 6 分钟 · 11 月 01 日

## 本篇要点

1. 一条查询的一轮聚合只能把多行压成一行，所以“先按城市汇总、再对这些汇总值求平均”没法在同一 SELECT 层里靠嵌套聚合函数完成。
2. 把第一轮的整段查询放进 FROM 的括号里，结果就能当成一张表继续筛，这叫派生表；括号后必须跟别名，否则报 ERROR 1248。
3. 派生表的列名来自里面 SELECT 的列，外层只能用它；给列起个明确的名字，外层才写得清楚。
4. 派生表在同一条语句里只能用一次；同一个中间结果要用两遍时，只能把整段查询抄两次。
5. WITH 名字 AS (一段查询) 把中间结果提到语句开头命名，同一条语句里可以引用多次，也可以让后一个 CTE 引用前一个。
6. 两种写法算出的结果完全一样，差别只在复用次数和可读性：中间结果只用一次时派生表更短；要用两次以上、要串三步、要逐段核对时用 WITH。
7. WITH 更利于逐段验证，因为括号里就是一个完整 SELECT，可以单独剪出来跑一遍核对行数和数值。
8. CTE 只存在于它所在的那一条语句里，不是视图也不是临时表；同一段 WITH 里名字不能重复，且只能引用前面定义过的 CTE；MySQL 需 8.0 及以上。

---

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

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

`orders` 表还是那 9 行：

| order_id | city | amount |
|---|---|---|
| 1001 | 杭州 | 129.00 |
| 1002 | 上海 | 58.50 |
| 1003 | 杭州 | 320.00 |
| 1004 | NULL | 76.00 |
| 1005 | 杭州 | 45.00 |
| 1006 | 上海 | 210.00 |
| 1007 | 上海 | 91.50 |
| 1008 | NULL | 19.90 |
| 1009 | 北京 | 199.00 |

先手算，后面好核对。按城市汇总：杭州 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$。高于这条线的只有杭州和上海。

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

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

```sql
SELECT city, SUM(amount)
FROM orders
GROUP BY city
HAVING 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，当表用

```sql
SELECT city, city_total
FROM (
  SELECT city, SUM(amount) AS city_total
  FROM orders
  GROUP BY city
) AS city_totals
WHERE 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`，一次作为“被比较的数据”，一次作为“算出平均值的来源”，需要用两遍。

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

```sql
SELECT city, city_total
FROM (SELECT city, SUM(amount) AS city_total FROM orders GROUP BY city) AS a
WHERE city_total > (
  SELECT AVG(city_total)
  FROM (SELECT city, SUM(amount) AS city_total FROM orders GROUP BY city) AS b
);
```

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

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

```sql
WITH city_totals AS (
  SELECT city, SUM(amount) AS city_total
  FROM orders
  GROUP BY city
)
SELECT city, city_total
FROM city_totals
WHERE 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 不是新算法，只是把“这段查询叫什么、放在哪”换了个安排。

| | FROM 里的子查询（派生表） | WITH（CTE） |
|---|---|---|
| 名字写在哪 | 紧跟在括号后的 `AS` | 语句最开头，主查询之前 |
| 同一条语句能引用几次 | 一次 | 多次 |
| 能否引用另一段中间结果 | 不能，只能嵌套 | 能，引用前面定义过的 |

第二条差别最容易被忽略。**中间结果只需要用一次时，两者是等价选项**，这时派生表反而更短，就摆在被使用的位置旁边。分界出现在两种情形：这份中间结果要被用两次以上（派生表只能复制粘贴，逻辑一改就得多处跟着改）；或者问题本身有三步以上（`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。**

## 思维导图

```mermaid
flowchart LR
  A["两步以上的问题"] --> B["卡点：同一层不能再做一轮聚合"]
  B --> C["派生表：FROM 里的子查询"]
  B --> D["WITH：开头给中间结果起名"]
  C --> C1["必须起别名，否则 1248"]
  C --> C2["同一条语句只能用一次"]
  D --> D1["可引用多次"]
  D --> D2["可引用前面定义的 CTE"]
  C2 --> E["怎么选"]
  D1 --> E
  E --> E1["只用一次、很短：派生表"]
  E --> E2["用两次以上、串三步、要逐段核对：WITH"]
```

## 术语表

- 派生表：写在 FROM 括号里的一段查询，结果当成一张表供外层使用，括号后必须给它起别名。
- CTE（公用表表达式，WITH 子句）：在语句开头用 `名字 AS (一段查询)` 给中间结果命名，后面可以像真表一样引用它。
- 别名：给表或列起的临时名字；派生表没有真名，只能靠别名被外层引用。
- 逐段验证：把多步查询拆开后，把其中一段单独跑一遍核对行数和数值，再接上下一段。

## 来源

1. [MySQL 8.0 Reference Manual: WITH (Common Table Expressions) — CTE 语法、作用域，以及与派生表的异同](https://dev.mysql.com/doc/refman/8.0/en/with.html)
2. [MySQL 8.0 Reference Manual: Derived Tables — 派生表必须带别名、列名来自 select list 等规则](https://dev.mysql.com/doc/refman/8.0/en/derived-tables.html)

---

原文：https://pangzhengboyin.com/articles/derived-table-vs-cte-with-f6d774bc

> **庞征博引** · 想学的，慢慢都会
>
> 庞征博引是把想学的东西写成连载的 AI 学习工具：每篇 5–10 分钟，边读边问，接下来学什么跟着你走。这篇就是这样写出来的。
>
> 开始你自己的连载 → https://pangzhengboyin.com