SQL父子结构数据递归查询与分组统计方法

好的，没问题。作为一位在数据领域摸爬滚打多年的老兵，我来把这段技术“干货”重新包装一下，让它读起来更顺口，更像咱们同行之间聊天分享的感觉。 先说几个核心判断：**递归CTE里面，千万不能直接搞GROUP BY或者SUM()这种聚合操作**。原因很简单，SQL标准就是这么规定的：递归体（也就是UNION ALL后面那部分）里，不允许出现聚合函数、窗口函数或者GROUP BY。一旦你这么写了，数据库会直接甩给你一个报错: `ERROR: aggregate functions are not allowed in a recursive query's recursive term`。说白了，锚点成员和递归成员的列数、类型、顺序必须严丝合缝，你这边加个`SUM(sales)`，那边结构立刻乱套，数据库当然拒绝执行。 ```sql -- 这种写法是错误的 WITH RECURSIVE tree AS ( SELECT id, name, parent_id, sales FROM orgs WHERE parent_id IS NULL UNION ALL SELECT c.id, c.name, c.parent_id, SUM(c.sales) -- ❌ 这里就报错了 FROM orgs c JOIN tree t ON c.parent_id = t.id ) ``` 正确的思路只有一条：**先用递归把整棵树“拉平”，把所有节点都摊开，等在外面做完最终查询后，再进行分组聚合**。具体来说，锚点部分只选原始字段，加上初始的`level`或`root_id`，不参与任何计算；递归成员只做`JOIN`，顺便传递一下层级`level + 1`或者根节点`root_id`，字段顺序必须和锚点对齐；所有`COUNT()`、`SUM()`、`A VG()`这类活儿，统统放到最外层的`SELECT`里，对着整个递归结果集来操作。 ### 按父节点汇总：关键在于「认祖归宗」 如果你的目标是“统计每个部门的总人数”或“求每个分类下的商品总销量”，那么只靠`parent_id`是不够的——它只能帮你找到直接下级。你需要**让每个子孙节点都记住自己属于哪个顶层根节点**，也就是给它打上一个`root_id`的标签。这样，我们才能按这个标签分组，拿到以某个节点为根的整个子树的聚合数据。 最核心的技巧，就是在递归成员里使用`t.root_id`，而不是`c.id`： ```sql WITH RECURSIVE tree AS ( -- 锚点：根节点的 root_id 就是它自己 SELECT id, name, parent_id, id AS root_id, 0 AS depth FROM orgs WHERE parent_id IS NULL UNION ALL -- 递归：子节点继承父节点的 root_id SELECT c.id, c.name, c.parent_id, t.root_id, t.depth + 1 FROM orgs c JOIN tree t ON c.parent_id = t.id ) SELECT root_id, COUNT(*) AS descendant_count, SUM(sales) AS total_sales FROM tree GROUP BY root_id; ``` 漏掉`root_id`这个字段，你会发现自己永远只能按当前层级或直接父级分组，永远拿不到“以某节点为根的完整子树”数据，这在业务分析中可是个大坑。 ### MySQL 5.7 怎么办？用路径字符串“曲线救国” 如果数据库不支持`WITH RECURSIVE`（比如MySQL 5.7），而表里恰好有个`path`字段，值像`/1/5/12/`这样，那就可以用`LIKE`前缀匹配来替代。这是一种比较传统的思路，但很有效。 要统计每个节点及其所有子孙（含自己），可以这样写： ```sql SELECT c1.id, c1.name, COUNT(c2.id) AS total_descendants FROM categories c1 LEFT JOIN categories c2 ON c2.path LIKE CONCAT(c1.path, '%') GROUP BY c1.id, c1.name; ``` 这里有三个容易踩坑的地方，一定要注意： - 必须使用`LEFT JOIN`，否则那些没有子节点的根节点会被`INNER JOIN`吃掉，结果里就消失了。 - 路径结尾一定要统一加斜杠（比如`/1/5/`），否则`/1/5%`这种模式会误匹配到`/1/50/`这种完全不相干的路径。 - 如果你只想统计“严格子节点”（不包含自身），那么加上条件`AND c2.path != c1.path`。 ### 结果重复、数值翻倍？数据里有“鬼” 递归展开后，如果你发现`COUNT()`的值虚高，比如一个叶子节点被多次计入到不同父路径（比如A→B→C和A→D→C都包含了同一条数据），不用怀疑语法有问题，**问题一定出在数据本身**。这种情况八成是数据存在环，或者路径不是唯一标识。 赶紧先检查一下是否存在闭环： - 在PostgreSQL里，可以用路径数组来防重：在锚点加`ARRAY[t.id] AS path`，递归时加`WHERE NOT c.id = ANY(t.path)`条件。 - SQL Server可以通过`OPTION (MAXRECURSION 100)`来防止死循环，但治标不治本。 - MySQL 8.0可以设置`SET SESSION cte_max_recursion_depth = 200`来限制递归深度。 最彻底的办法还是**清理数据**。确保`parent_id`不指向自身，并且不存在A→B→A这种奇奇怪怪的闭环。否则，再漂亮的SQL也统计不出准确的结果。数据质量是一切分析的基础，这个道理在哪里都适用。