公用表表达式（CTE）是一个被你提取出来并命名的子查询。你不再把一个 SELECT 嵌套在另一个 SELECT 里，而是在最前面用 WITH 定义它、给它命名，然后在主查询中像使用表一样使用这个名字。
它的结构总是这样：
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
('Ada', 120), ('Boris', 45), ('Ada', 80),
('Carla', 200), ('Boris', 30), ('Ada', 50);
WITH customer_totals AS (
SELECT customer, SUM(amount) AS total
FROM orders
GROUP BY customer
)
SELECT customer, total
FROM customer_totals
WHERE total > 100;
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
('Ada', 120), ('Boris', 45), ('Ada', 80),
('Carla', 200), ('Boris', 30), ('Ada', 50);
SELECT customer, total
FROM (
SELECT customer, SUM(amount) AS total
FROM orders
GROUP BY customer
)
WHERE total > 100;
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
('Ada', 120), ('Boris', 45), ('Ada', 80),
('Carla', 200), ('Boris', 30), ('Ada', 50);
WITH
customer_totals AS (
SELECT customer, SUM(amount) AS total
FROM orders
GROUP BY customer
),
big_spenders AS (
SELECT customer, total
FROM customer_totals
WHERE total > 100
)
SELECT customer, total
FROM big_spenders
ORDER BY total DESC;
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
('Ada', 120), ('Boris', 45), ('Ada', 80),
('Carla', 200), ('Boris', 30), ('Ada', 50);
WITH customer_totals AS (
SELECT customer, SUM(amount) AS total
FROM orders
GROUP BY customer
)
SELECT
customer,
total,
total - (SELECT AVG(total) FROM customer_totals) AS diff_from_avg
FROM customer_totals
ORDER BY diff_from_avg DESC;
这个 CTE 被引用了两次：一次用来计算平均值，一次作为主数据源。如果不用 CTE，你就得把 GROUP BY 查询写两遍，以后每次修改都要改两个地方。
在 INSERT、UPDATE 和 DELETE 中使用 CTE
CTE 不只用于 SELECT。你可以在 INSERT、UPDATE 或 DELETE 前面加上 WITH 子句，在写操作中使用具名子查询：
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
CREATE TABLE flagged_customers (customer TEXT, total REAL);
INSERT INTO orders (customer, amount) VALUES
('Ada', 120), ('Boris', 45), ('Ada', 80),
('Carla', 200), ('Boris', 30);
WITH customer_totals AS (
SELECT customer, SUM(amount) AS total
FROM orders
GROUP BY customer
)
INSERT INTO flagged_customers (customer, total)
SELECT customer, total
FROM customer_totals
WHERE total > 100;
SELECT * FROM flagged_customers;
CTE 描述了要标记哪些行，INSERT ... SELECT 把它当作数据源。同样的技巧也适用于 DELETE FROM ... WHERE id IN (SELECT id FROM cte)，在筛选逻辑比较复杂时分步完成删除。
只在一个地方用到的简单子查询。WHERE id IN (SELECT id FROM ...) 保持原样就好。
性能关键的查询，并且你已经验证过把逻辑内联写会更快。和其他一些数据库相比，SQLite 通常不会那么激进地把 CTE 当作查询优化的屏障，但对于热点路径，还是值得用 EXPLAIN QUERY PLAN 检查一下。
完整示例
把这些串起来：一个小报表，找出每位客户最大的一笔订单，并与他们的平均订单金额作比较：
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
('Ada', 120), ('Boris', 45), ('Ada', 80),
('Carla', 200), ('Boris', 30), ('Ada', 50),
('Carla', 75), ('Boris', 90);
WITH
per_customer AS (
SELECT customer, MAX(amount) AS biggest, AVG(amount) AS average
FROM orders
GROUP BY customer
),
with_ratio AS (
SELECT customer, biggest, average, biggest / average AS ratio
FROM per_customer
)
SELECT customer, biggest, ROUND(average, 2) AS average, ROUND(ratio, 2) AS ratio
FROM with_ratio
ORDER BY ratio DESC;
