MySQL 公用表表达式（CTE）

使用WITH子句编写更清晰的递归和非递归查询。 · 难度：入门 · +15XP

MySQL 公用表表达式（CTE）

CTE（Common Table Expression）是MySQL 8.0引入的临时结果集，使用WITH子句定义，在同一查询中可多次引用，提高SQL可读性和复用性。

非递归CTE

WITH sales_summary AS (
    SELECT department, SUM(amount) AS total_sales
    FROM orders
    GROUP BY department
)
SELECT * FROM sales_summary WHERE total_sales > 10000;

递归CTE

递归CTE用于处理树形结构数据，如组织架构、分类层级。

WITH RECURSIVE cte AS (
    -- 初始查询（锚点）
    SELECT id, name, parent_id, 1 AS level
    FROM categories WHERE parent_id IS NULL
    UNION ALL
    -- 递归查询
    SELECT c.id, c.name, c.parent_id, cte.level + 1
    FROM categories c
    INNER JOIN cte ON c.parent_id = cte.id
)
SELECT * FROM cte;

CTE与子查询对比

• CTE可被多次引用，避免重复子查询
• 递归CTE是实现层次查询的唯一方式
• CTE使逻辑更清晰，易于调试

练习提示

创建一个类别表，使用递归CTE查询所有子分类及其层级深度。或者使用非递归CTE计算各部门的平均薪资并与整体平均比较。