SQL 实战：使用 CTE（公用表达式）优化递归与多层复杂查询

公用表表达式（Common Table Expression，CTE） 是 SQL 中用于简化复杂查询和递归操作的强大工具。CTE 提供了一种临时结果集，可以在查询中多次引用，避免重复代码，提高查询的可读性和维护性。

CTE 主要应用于以下场景：

1. 递归查询：生成层级关系树，如员工上下级关系、用户推荐关系等。
2. 分层累计计算：实现逐层汇总与动态累计。
3. 复杂子查询优化：通过 CTE 拆分复杂的多层嵌套查询，提高执行效率。

一、CTE 语法与基本概念

1. CTE 语法结构

WITH cte_name AS (  
    SELECT column1, column2  
    FROM table_name  
    WHERE condition  
)  
SELECT * FROM cte_name;

说明：

• WITH 关键字用于定义 CTE，cte_name 是 CTE 名称。
• CTE 可以作为子查询使用，后续主查询可以直接调用 CTE。

2. 递归 CTE 语法

WITH RECURSIVE cte_name AS (  
    -- 基础查询（递归起点）  
    SELECT column1, column2, 1 AS level  
    FROM table_name  
    WHERE condition  
  
    UNION ALL  
  
    -- 递归部分（自引用）  
    SELECT t.column1, t.column2, c.level + 1  
    FROM table_name t  
    JOIN cte_name c  
    ON t.parent_id = c.id  
)  
SELECT * FROM cte_name;

说明：

• 递归 CTE 包括基础查询和递归查询，通过 UNION ALL 将两部分合并。
• 递归 CTE 适用于生成树形结构或层次关系的查询。

二、实战案例

案例 1：生成用户推荐关系的递归树

需求描述：
在用户管理系统中，每个用户可能通过其他用户推荐注册。我们需要生成用户推荐树，展示层级关系，并计算每个用户对应层级的奖励金额。

表结构 users

需求目标

• 生成完整的推荐树（用户层级关系）。
• 计算每个用户的层级。
• 汇总每层用户的奖励金额。

SQL 实现

WITH RECURSIVE user_tree AS (  
    -- 基础层（顶层推荐人）  
    SELECT user_id, name, referrer_id, amount, 1 AS level  
    FROM users  
    WHERE referrer_id IS NULL  
  
    UNION ALL  
  
    -- 递归计算下级用户  
    SELECT u.user_id, u.name, u.referrer_id, u.amount, t.level + 1  
    FROM users u  
    JOIN user_tree t  
    ON u.referrer_id = t.user_id  
)  
SELECT user_id, name, referrer_id, level, amount  
FROM user_tree  
ORDER BY level, referrer_id;

查询结果

解释：

• 初始查询筛选出没有推荐人的用户（顶层推荐人）。
• 递归部分根据 referrer_id 逐层匹配，计算每个用户的层级。
• level 字段表示用户所在的层级深度。

案例 2：动态计算每层用户奖励金额累计

需求描述：
在推荐系统中，每层用户的订单金额需要累计汇总，以便按层级计算奖励发放。

SQL 实现

WITH RECURSIVE user_tree AS (  
    SELECT user_id, referrer_id, amount, 1 AS level  
    FROM users  
    WHERE referrer_id IS NULL  
  
    UNION ALL  
  
    SELECT u.user_id, u.referrer_id, u.amount, t.level + 1  
    FROM users u  
    JOIN user_tree t  
    ON u.referrer_id = t.user_id  
),  
amount_summary AS (  
    SELECT level, SUM(amount) AS total_amount  
    FROM user_tree  
    GROUP BY level  
)  
SELECT level, total_amount  
FROM amount_summary  
ORDER BY level;

查询结果

解释：

• 递归 CTE 生成用户推荐树后，第二个 CTE amount_summary 按层级汇总每层用户的订单金额。
• 通过 SUM(amount) 和 GROUP BY level 计算每个层级的总金额。

三、复杂子查询优化与简化

案例 3：优化嵌套子查询，提取每个部门的最新员工入职记录

需求描述：
在员工管理系统中，每个部门可能有多个员工，我们需要提取每个部门最新入职的员工记录。

表结构 employees

SQL 实现

WITH latest_hires AS (  
    SELECT dept_id, MAX(hire_date) AS max_hire_date  
    FROM employees  
    GROUP BY dept_id  
)  
SELECT e.*  
FROM employees e  
JOIN latest_hires l  
ON e.dept_id = l.dept_id  
AND e.hire_date = l.max_hire_date;

查询结果

解释：

• 第一个 CTE latest_hires 计算每个部门的最新入职时间。
• 通过 JOIN 筛选出最新入职的员工记录。

四、总结

• CTE 简化复杂查询：将复杂子查询或递归逻辑拆分成易读的步骤，提高代码可维护性。
• 递归 CTE 是生成层次结构（如树形关系、组织架构）的理想工具。
• CTE 支持嵌套和多层次查询，可以轻松解决分层汇总、动态累计等问题，提高查询性能和可读性。