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 的真实价值所在。
