在SQL的实际业务场景中，我们经常会遇到需要按照分组逻辑对层级结构数据进行递归求和的需求，比如统计每个部门及其所有下属部门的员工总薪资，或者计算商品分类树中每个分类及其子分类的总销售额。这类需求如果单纯使用普通的分组查询很难实现，而递归CTE（公用表表达式）可以很好地解决这个问题。
递归CTE基础语法
递归CTE是SQL标准中支持的递归查询语法，主要由两部分组成：锚定部分和非递归部分，两部分通过UNION ALL连接。锚定部分用于获取递归的初始数据集，非递归部分用于基于上一次递归的结果继续查询下一层级的数据，直到没有新数据返回为止。
基础语法结构如下：
-- 定义递归CTE
WITH RECURSIVE cte_name AS (
-- 锚定部分：初始数据集
SELECT column1, column2, ...
FROM table_name
WHERE 初始条件
UNION ALL
-- 递归部分：基于上一次结果查询下一层级
SELECT t.column1, t.column2, ...
FROM table_name t
INNER JOIN cte_name c ON t.关联字段 = c.关联字段
)
-- 使用CTE查询结果
SELECT * FROM cte_name;
分组逻辑下的递归求和实现思路
要在分组逻辑下实现递归求和，核心思路是先通过递归CTE构建出完整的层级关系数据，再结合分组逻辑对每一层级的对应数据进行汇总。具体可以分为三个步骤：
- 第一步：通过递归CTE生成包含完整层级路径的数据集，明确每个节点对应的父节点和层级深度
- 第二步：根据分组字段对递归生成的数据集进行分组，确定每个分组对应的所有层级节点
- 第三步：对每个分组下的所有节点的目标求和字段进行累加，得到最终的递归求和结果
实际案例演示
假设我们有一个部门员工表dept_emp，表结构如下：
| 字段名 | 类型 | 说明 |
|---|---|---|
| dept_id | INT | 部门ID |
| dept_name | VARCHAR(50) | 部门名称 |
| parent_dept_id | INT | 上级部门ID，根部门为NULL |
| emp_salary | DECIMAL(10,2) | 部门直属员工总薪资 |
现在需要统计每个部门及其所有下属部门的总薪资，也就是层级汇总每个部门的薪资总和。首先我们通过递归CTE生成每个部门对应的所有下属部门ID列表：
WITH RECURSIVE dept_hierarchy AS (
-- 锚定部分：获取所有部门作为初始节点
SELECT
dept_id AS root_dept_id,
dept_id AS sub_dept_id,
dept_name,
emp_salary
FROM dept_emp
UNION ALL
-- 递归部分：关联查询下属部门
SELECT
h.root_dept_id,
d.dept_id AS sub_dept_id,
d.dept_name,
d.emp_salary
FROM dept_hierarchy h
INNER JOIN dept_emp d ON d.parent_dept_id = h.sub_dept_id
)
-- 按根部门分组求和
SELECT
root_dept_id,
MAX(dept_name) AS dept_name,
SUM(emp_salary) AS total_salary
FROM dept_hierarchy
GROUP BY root_dept_id
ORDER BY root_dept_id;
上面的查询中，dept_hierarchy这个递归CTE会为每个部门生成一条记录，记录该部门作为根部门时，所有下属部门的ID和薪资。之后我们按照root_dept_id分组，对emp_salary求和，就得到了每个部门及其所有下属部门的总薪资。
注意事项
使用递归CTE进行分组递归求和时，需要注意以下几点：
- 不是所有数据库都支持递归CTE，比如MySQL从8.0版本开始支持，SQL Server、PostgreSQL、Oracle也都支持，使用时需要确认数据库版本
- 递归部分要避免出现无限递归的情况，比如层级数据中存在循环引用时，需要添加递归深度限制，不同数据库的限制方式不同，比如MySQL可以通过cte_max_recursion_depth参数控制最大递归深度
- 如果分组字段和层级字段不是同一个，需要先明确分组和层级的关联关系，避免求和结果错误
递归CTE是非常强大的SQL功能，除了递归求和之外，还可以用于层级路径查询、树形结构遍历等场景，掌握它的使用方法可以解决很多复杂的SQL查询问题。
如果需要在递归过程中同时保留层级深度信息，还可以在递归CTE中添加一个层级深度的字段，每次递归时深度加1，方便后续根据深度进行过滤或者排序。
WITH RECURSIVE dept_hierarchy AS (
SELECT
dept_id AS root_dept_id,
dept_id AS sub_dept_id,
dept_name,
emp_salary,
0 AS depth -- 初始层级深度为0
FROM dept_emp
UNION ALL
SELECT
h.root_dept_id,
d.dept_id AS sub_dept_id,
d.dept_name,
d.emp_salary,
h.depth + 1 -- 每次递归深度加1
FROM dept_hierarchy h
INNER JOIN dept_emp d ON d.parent_dept_id = h.sub_dept_id
)
SELECT
root_dept_id,
MAX(dept_name) AS dept_name,
SUM(emp_salary) AS total_salary,
MAX(depth) AS max_depth
FROM dept_hierarchy
GROUP BY root_dept_id
ORDER BY root_dept_id;