WITH

第 16 课，共 21 课 · 第 4 部分 · 更强的查询 · 约 15 分钟

上一课把子查询放进 FROM，当成一张表来用。查询一长，括号套括号，就很难读了。WITH 让你先把这段查询拿出来，起个名字，再在下面用。

一条难读的查询

下面查出每个下过单的客户花了多少钱。中间那一大段括号是派生表 s，先算出每个客户编号花的钱，外面再接上 customers 拿到名字：

main.sql

1SELECT c.name, s.spent2FROM customers AS c3JOIN (4    SELECT o.customer_id, SUM(oi.quantity * oi.unit_price) AS spent5    FROM orders AS o6    JOIN order_items AS oi ON oi.order_id = o.id7    GROUP BY o.customer_id8) AS s ON s.customer_id = c.id9ORDER BY s.spent DESC;

输出

name	spent
林晓	116.50
王磊	101.80
孙强	59.00
刘洋	55.80
张伟	54.60
周婷	23.50
陈静	22.50

7 行

能跑，可读的时候得先跳进括号里看懂 s 是什么，再跳出来接着读。

WITH 先起个名字

同一条查询，用 WITH 写：

main.sql

1WITH spending AS (2    SELECT o.customer_id, SUM(oi.quantity * oi.unit_price) AS spent3    FROM orders AS o4    JOIN order_items AS oi ON oi.order_id = o.id5    GROUP BY o.customer_id6)7SELECT c.name, s.spent8FROM customers AS c9JOIN spending AS s ON s.customer_id = c.id10ORDER BY s.spent DESC;

输出

name	spent
林晓	116.50
王磊	101.80
孙强	59.00
刘洋	55.80
张伟	54.60
周婷	23.50
陈静	22.50

7 行

WITH 名字 AS (查询) 先定义一张临时的结果表 spending，下面的 SELECT 就像用一张普通的表一样用它。结果一模一样，可现在从上往下读就行：先算每人花了多少，再接上名字。这样起了名字的查询叫 CTE（公用表表达式），名字起得好，一眼就知道它是什么。MySQL 要 8.0 以上才能用 WITH。

先猜一猜

结果是几？

1WITH cheap AS (2    SELECT * FROM products WHERE price < 103)4SELECT COUNT(*) FROM cheap WHERE category = '文具';

cheap 里是价格低于 10 的 4 件商品：笔记本、中性笔、数据线、咖啡杯。下面只数其中的文具，是 2 件。

用两次

哪些客户花的钱比平均多？要用 spending 两次：一次算平均，一次拿每个人去比。用 WITH，这段查询只写一遍：

main.sql

1WITH spending AS (2    SELECT o.customer_id, SUM(oi.quantity * oi.unit_price) AS spent3    FROM orders AS o4    JOIN order_items AS oi ON oi.order_id = o.id5    GROUP BY o.customer_id6)7SELECT c.name, s.spent8FROM spending AS s9JOIN customers AS c ON c.id = s.customer_id10WHERE s.spent > (SELECT AVG(spent) FROM spending)11ORDER BY s.spent DESC;

输出

name	spent
林晓	116.50
王磊	101.80

2 行

不用 WITH 的话，同样一大段要在 FROM 和子查询里各抄一遍，改的时候还得两处一起改。

一次定义几个

WITH 后面可以定义好几个，用逗号隔开，WITH 只写一次。后面的还能用前面的：

main.sql

1WITH order_totals AS (2    SELECT order_id, SUM(quantity * unit_price) AS total3    FROM order_items4    GROUP BY order_id5),6shipped AS (7    SELECT t.order_id, t.total8    FROM order_totals AS t9    JOIN orders AS o ON o.id = t.order_id10    WHERE o.status = '已发货'11)12SELECT COUNT(*) AS orders, SUM(total) AS revenue13FROM shipped;

输出

orders	revenue
8	296.20

1 行

order_totals 算出每个订单的总额，shipped 从里面挑出已发货的，最后数一数、加一加。每一段只做一件事，像写步骤一样。

只在这一条里有效

WITH 定义的名字只属于跟在它后面的那一条查询。分号一到，它就没了：

main.sql

1WITH cheap AS (SELECT * FROM products WHERE price < 10)2SELECT COUNT(*) FROM cheap;3SELECT * FROM cheap;

输出

COUNT(*)
4

1 行

ERROR 1146 (42S02): Table 'town.cheap' doesn't exist

没有叫「cheap」的表。先 CREATE TABLE 建表，或者检查一下表名有没有写错。

第一条查出 4；第二条是新的一条查询，cheap 已经不存在了，ERROR 1146 说找不到这张表。

什么时候用

子查询短，用一次：直接写在括号里就好。
子查询长，或者要用好几次，或者要分好几步算：用 WITH，一步一个名字。

WITH 还有一种 WITH RECURSIVE，能一层一层地查下去（比如员工的上级的上级），这门课不讲。

练一练

写好查询，点检查。提示随便看，实在想不出来再看答案。

练习 1

大订单

用 WITH 定义 order_totals（每个订单的 order_id 和总额 total），查出总额超过 50 的订单：order_id 和 total 两列，按 total 从高到低排。

要显示

order_id	total
3	76.00
7	61.90
8	59.00

3 行

exercise1.sql

1SELECT order_id, quantity * unit_price AS amount2FROM order_items;

输出

点「运行」或按 Ctrl+Enter

练习 2

高于平均的订单

查出总额高于所有订单平均总额的订单：order_id 和 total 两列，按 order_id 排序。

要显示

order_id	total
2	39.90
3	76.00
5	43.80
7	61.90
8	59.00
12	39.60

6 行

exercise2.sql

1WITH order_totals AS (2    SELECT order_id, SUM(quantity * unit_price) AS total3    FROM order_items4    GROUP BY order_id5)6SELECT order_id, total7FROM order_totals8ORDER BY order_id;

输出

点「运行」或按 Ctrl+Enter

练习 3

每个城市的销售额

用起始代码里的 spending，算出每个城市的客户一共花了多少（取消的订单也算）：city 和 revenue 两列，按 revenue 从高到低排。

要显示

city	revenue
上海	162.50
广州	113.60
北京	101.80
成都	55.80

4 行

exercise3.sql

1WITH spending AS (2    SELECT o.customer_id, SUM(oi.quantity * oi.unit_price) AS spent3    FROM orders AS o4    JOIN order_items AS oi ON oi.order_id = o.id5    GROUP BY o.customer_id6)7SELECT *8FROM spending;

输出

点「运行」或按 Ctrl+Enter

这一课学会了

WITH 名字 AS (查询) 先给一段查询起名字，下面像用表一样用它。
长查询用 WITH 写，能从上往下一步一步读。
同一段结果要用好几次时，用 WITH 只写一遍。
一次定义几个，用逗号隔开；后面的可以用前面的。
WITH 的名字只在跟着它的那一条查询里有效。