WITH子句应单一职责、业务命名、避免缩写、小写下划线、逗号分隔、顺序引用、禁循环依赖、显式列名、GROUP BY合规、递归慎用——重在可读性与协作效率。

每个WITH子句只做一件事，命名必须带业务含义

要把 WITH 当作变量声明来用，而不是换行工具。比如说，active_users_30d 只用于过滤用户状态和时间范围，不进行订单的JOIN操作；shipped_orders 仅获取 status = 'shipped' 的订单，不查询用户字段。

命名别用 tmp1、subq_a 这类代号，而要体现数据含义和时效性：daily_active_users_jul2024 比 daus 更安全，尤其当多人协作或后期维护时。

• 链式依赖可加前缀：如 user_base → user_with_ltv → high_value_cohort，一眼看出计算流向
• 避免缩写歧义：rev 可能是 revenue、review 或 reversal；revenue_q2_2024 不会猜错
• MySQL 8.0+ 和 PostgreSQL 对大小写敏感策略不同，统一用小写下划线命名最稳妥

多个CTE必须用逗号分隔，且引用顺序不能倒置

写多个 CTE 时，AS 后面必须紧跟括号，各 CTE 之间用英文逗号隔开——漏掉逗号在 MySQL 8.0+ 里直接报错 ERROR 1064 (42000)。

CTE 执行顺序严格从上到下，后面定义的 CTE 不能被前面的引用。例如：

WITH
order_counts AS (SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id),
high_freq_users AS (SELECT user_id FROM order_counts WHERE cnt > 5)--  正确：order_counts 已定义
SELECT * FROM high_freq_users;

但下面这样就会失败：

WITH
high_freq_users AS (SELECT user_id FROM order_counts WHERE cnt > 5),--  报错：order_counts 尚未定义
order_counts AS (SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id)
SELECT * FROM high_freq_users;

• 不能循环依赖：A 引用 B，B 又引用 A —— 数据库直接拒绝解析
• PostgreSQL 允许在同一个 WITH 块里跨 CTE 引用（只要顺序对），MySQL 8.0+ 也支持，但 SQLite 旧版本不支持多 CTE
• 别名冲突优先级：如果 CTE 名和物理表同名（比如都叫 users），MySQL 默认选物理表；PostgreSQL 则优先选 CTE，行为不一致需警惕

SELECT 必须显式列名，禁用 SELECT *

SELECT *在CTE后主查询中简直是个“大坑”：由于CTE内部的GROUP BY或者引擎优化，字段顺序可能会发生变化，尤其是在跨数据库迁移的时候，很容易出现错位的情况。更严重的是，如果JOIN了多张表却没有加上前缀，马上就会触发Column 'id' is ambiguous错误。

正确做法是：每个 CTE 的 AS 后换行写 SELECT，字段分行对齐，显式写出所有要用的列。

WITH
shipped_orders AS (
SELECT
order_id,
user_id,
amount,
created_at
FROM orders
WHERE status = 'shipped'
)
SELECT
u.name,
so.amount,
so.created_at
FROM users u
JOIN shipped_orders so ON u.id = so.user_id;

• CTE 中若含 GROUP BY，所有非聚合字段必须出现在 GROUP BY 列表里，否则 MySQL 8.0+ 严格模式下报 ERROR 1055 (42000)
• 字段别名要在 CTE 内部定义好，主查询直接用别名，别在主查询里再 AS 一次——易导致重复重命名或覆盖
• CTE 定义时不声明列名（如 WITH x AS (SELECT a+b)），后续引用时字段名为 expr_1 这类系统生成名，极难调试

递归WITH RECURSIVE不是语法装饰，只用于真正不确定层级的场景

看到“上级-下级”结构就加 RECURSIVE，是中级 SQL 用户最常踩的坑。它不是高级勋章，而是专治树形遍历的手术刀。滥用会导致无限循环、栈溢出，或返回意料之外的中间结果。

必须同时满足三项才考虑递归 CTE：

• 数据本身是自关联结构（如 employees.manager_id → employees.id）
• 层级深度不可预知（不能用 3 层 JOIN 硬写死）
• 需要逐层展开路径（比如查某员工的所有下属，含间接下属）

锚点（anchor）和递归部分必须用 UNION ALL 连接，且递归引用只能出现在 FROM 子句中，别名必须和 CTE 名一致：

WITH RECURSIVE org_tree AS (
SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL-- 锚点：顶层
UNION ALL
SELECT e.id, e.name, e.manager_id, ot.level + 1
FROM employees e
INNER JOIN org_tree ot ON e.manager_id = ot.id--  正确：引用自身别名
)
SELECT * FROM org_tree;

注意：MySQL 默认递归深度限制为 100，超限报 ERROR 3636 (HY000)；PostgreSQL 是 stack depth limit exceeded。调高前先确认逻辑是否真需要那么深。

真正容易被忽略的点是：CTE 本身不物化——多数引擎只是语法重写，性能未必提升。你花十分钟优化命名和拆分逻辑，换来的是别人三秒看懂、两分钟改对，这才是 WITH 的真实价值所在。

《夸克》非常好用的免费AI浏览器

下载APP查看