5 分钟搞懂 CTE（WITH 子句）

CTE (Common Table Expression) 是 MariaDB 10.2+ 引入的标准 SQL 语法，能极大改善查询可读性，并解锁递归查询。

基本用法

WITH recent_orders AS (
  SELECT * FROM orders WHERE created_at > NOW() - INTERVAL 7 DAY
)
SELECT user_id, COUNT(*) AS cnt
FROM recent_orders
GROUP BY user_id;

等价于子查询，但人类好读多了。

多 CTE

WITH
  recent AS (SELECT * FROM orders WHERE created_at > NOW() - INTERVAL 7 DAY),
  big AS (SELECT * FROM recent WHERE amount > 1000)
SELECT u.name, COUNT(b.id)
FROM users u LEFT JOIN big b ON b.user_id = u.id
GROUP BY u.id;

递归 CTE

经典场景：组织树、评论树、分类树、找最短路径。

-- 找某员工的全部上级链
CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(64), boss_id INT);

WITH RECURSIVE chain AS (
  SELECT id, name, boss_id, 0 AS depth
  FROM employees WHERE id = 42                    -- 起点
  UNION ALL
  SELECT e.id, e.name, e.boss_id, c.depth + 1
  FROM employees e JOIN chain c ON e.id = c.boss_id   -- 递归
)
SELECT * FROM chain;

输出：

id	name	boss_id	depth
42	alice	7	0
7	bob	3	1
3	carol	NULL	2

生成日期序列

WITH RECURSIVE dates AS (
  SELECT DATE '2026-01-01' AS d
  UNION ALL
  SELECT d + INTERVAL 1 DAY FROM dates WHERE d < '2026-01-31'
)
SELECT d FROM dates;

适合做"按天聚合时填补缺失日期"。

CTE vs 子查询性能

MariaDB 10.6+ 优化器对 CTE 做了"merging"——简单 CTE 会被内联到主查询，几乎没有性能损失。复杂的递归 CTE 会物化（materialize）成临时表。

EXPLAIN
WITH recent AS (SELECT * FROM orders WHERE created_at > NOW() - INTERVAL 7 DAY)
SELECT * FROM recent WHERE amount > 1000;

看 Extra 列里有没有 "Materialize"。

限制

递归 CTE 默认深度上限 1000（cte_max_recursion_depth），防死循环。要更深手动调整。