| 用途 | 查询结构化与递归展开 |
|---|---|
| 语法 | WITH name AS (SELECT …) SELECT … |
| 递归 | WITH RECURSIVE cte AS (锚点 UNION ALL 递归部分) |
基础 CTE
基础 CTE
WITH top_users AS (
SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id HAVING total > 10000
)
SELECT u.name, t.total FROM top_users t JOIN users u ON u.id = t.user_id;递归展开层级
递归展开层级
WITH RECURSIVE tree AS (
SELECT id, name, parent_id, 1 AS lvl FROM categories WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.name, c.parent_id, t.lvl + 1
FROM categories c JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree ORDER BY lvl, id;递归 CTE 默认递归深度上限 cte_max_recursion_depth=1000（8.0），有环数据要先做防环处理。