用 WITH 把你的查询拆分成一个个具名步骤，并像一条转换流水线那样串联多个 CTE。
在 Kodokon 中打开本课嵌套了三层的子查询要从里往外读：既难以阅读，六个月后也没法再回头看懂。CTE（Common Table Expression，公用表表达式，即 WITH 子句）颠倒了阅读顺序：你像给函数命名那样给每个中间步骤起名字，查询就能自上而下地读下来。把这份数据集载入 sqliteonline.com 或 sqlite3。
CREATE TABLE payments (
id INTEGER PRIMARY KEY,
customer TEXT NOT NULL,
paid_at TEXT NOT NULL,
amount REAL NOT NULL
);
INSERT INTO payments (customer, paid_at, amount)
VALUES
('alice', '2026-01-10', 120.0),
('bruno', '2026-01-22', 80.0),
('alice', '2026-02-05', 60.0),
('chloe', '2026-02-18', 200.0),
('bruno', '2026-03-03', 40.0),
('alice', '2026-03-15', 90.0),
('chloe', '2026-03-28', 150.0),
('bruno', '2026-04-09', 300.0);基本语法：WITH name AS (SELECT ...)，后面跟着主查询，主查询把 name 当作一张普通的表来用。这里，我们用 substr 从日期中提取出月份，从而计算每月的营收（SQLite 的日期是 ISO 8601 文本，这是引擎的选择，而非某种标准）。
WITH monthly AS (
SELECT substr(paid_at, 1, 7) AS month,
SUM(amount) AS revenue
FROM payments
GROUP BY month
)
SELECT * FROM monthly ORDER BY month;真正的威力来自串联：一个 WITH、用逗号隔开的多个 CTE，每个都能读取在它之前定义的那些。你搭建起一条流水线：聚合、计算统计量、进行比较。这里，我们查找营收超过月平均值的月份（预期结果：2026-03 和 2026-04，平均值为 260）。
WITH monthly AS (
SELECT substr(paid_at, 1, 7) AS month,
SUM(amount) AS revenue
FROM payments
GROUP BY month
),
stats AS (
SELECT AVG(revenue) AS avg_revenue
FROM monthly
)
SELECT m.month, m.revenue,
ROUND(s.avg_revenue, 1) AS avg_all
FROM monthly AS m
CROSS JOIN stats AS s
WHERE m.revenue > s.avg_revenue
ORDER BY m.month;