MySQL 8.0: WITH子句

一、CTE 的核心概念与语法

定义：
CTE（Common Table Expression）是一种临时的命名结果集，仅在当前查询中有效。它通过 WITH 关键字定义，用于分解复杂查询、提高可读性和复用逻辑。

基本语法：

WITH cte_name (列1, 列2) AS (
    SELECT ...  -- 子查询
)
SELECT * FROM cte_name;  -- 主查询引用 CTE

• 多 CTE 定义：用逗号分隔多个 CTE

  WITH cte1 AS (...), cte2 AS (...) SELECT ...
  

• 递归 CTE：使用 WITH RECURSIVE 处理层级数据（如组织结构）[citation:1][citation:5]。

---

二、典型使用场景与示例

1. 简化复杂查询

场景：多层嵌套的子查询难以维护时，CTE 可拆分为逻辑模块。
示例：统计客户订单总金额并筛选高价值客户

WITH customer_totals AS (
    SELECT customer_id, SUM(amount) AS total_amount
    FROM orders
    GROUP BY customer_id
)
SELECT customer_id, total_amount
FROM customer_totals
WHERE total_amount > 1000;  -- 复用 CTE 结果[citation:1][citation:3]

2. 递归查询层级数据

场景：处理树状结构（如员工汇报链、分类目录）。
示例：查询员工及其所有下属

WITH RECURSIVE employee_hierarchy AS (
    -- 初始查询：顶层管理者
    SELECT employee_id, name, manager_id
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    -- 递归部分：逐级关联下属
    SELECT e.employee_id, e.name, e.manager_id
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;  -- 输出完整层级[citation:1][citation:5]

3. 分步计算与复用

场景：多步骤数据处理（如先聚合再关联）。
示例：计算部门薪资总和及平均年龄

WITH dept_salary AS (
    SELECT department_id, SUM(salary) AS total_salary
    FROM employees GROUP BY department_id
),
dept_age AS (
    SELECT department_id, AVG(age) AS avg_age
    FROM employees GROUP BY department_id
)
-- 关联两个 CTE
SELECT s.department_id, s.total_salary, a.avg_age
FROM dept_salary s
JOIN dept_age a ON s.department_id = a.department_id; [citation:4][citation:8]

---

三、MyBatis 中集成 CTE 的实践

MyBatis 支持在 XML 或注解中直接编写 CTE 语句。

1. XML 映射文件示例

<select id="getHighValueCustomers" resultType="map">
    WITH customer_totals AS (
        SELECT customer_id, SUM(amount) AS total_amount
        FROM orders
        GROUP BY customer_id
    )
    SELECT customer_id, total_amount
    FROM customer_totals
    WHERE total_amount > #{minAmount}
</select>

**2. 动态 CTE **

结合 MyBatis 动态 SQL（如 <if>）实现条件化 CTE：

<select id="getEmployeeHierarchy" resultType="Employee">
    WITH RECURSIVE employee_hierarchy AS (
        SELECT employee_id, name, manager_id
        FROM employees
        WHERE manager_id IS NULL
        <if test="deptId != null">
            AND department_id = #{deptId}  -- 动态添加部门过滤
        </if>
        UNION ALL
        ...
    )
    SELECT * FROM employee_hierarchy
</select>

---

四、CTE 的注意事项与优化

1. 性能优化：
   • 为 CTE 中频繁筛选的列创建索引。
   • 避免过度递归：设置 MAX_RECURSION 层级限制。