MySQL同表递归查询：统计指定父订单的子订单总数

需求回顾 #

你需要统计每个根订阅订单（Generated by为NULL、Order_type为Subscription）的所有多级子订单总数，最终得到如下结果：

假设你的表名为orders，字段对应为order_id、generated_by、order_type（建议把带空格的字段名改成下划线格式，避免SQL语法问题）。

递归查询实现代码 #

MySQL 8.0及以上版本支持WITH RECURSIVE语法，可以轻松实现多级递归查询，具体代码如下：

WITH RECURSIVE order_hierarchy AS (
    -- 锚点成员：筛选所有根订阅订单
    SELECT 
        order_id AS root_order,
        order_id AS child_order
    FROM orders
    WHERE generated_by IS NULL 
      AND order_type = 'Subscription'
    
    UNION ALL
    
    -- 递归成员：逐级关联子订单
    SELECT 
        oh.root_order,
        o.order_id AS child_order
    FROM order_hierarchy oh
    JOIN orders o ON oh.child_order = o.generated_by
    WHERE o.order_type = 'Auto_renewal' -- 只统计自动续费类型的子订单
)
-- 统计每个根订单的子订单总数
SELECT 
    root_order AS Order_id,
    COUNT(child_order) - 1 AS Count_of_children -- 减1是因为锚点里包含了根订单本身
FROM order_hierarchy
GROUP BY root_order
ORDER BY root_order;

代码解释 #

1. 锚点成员：先筛选出所有的根订阅订单（也就是generated_by为NULL的订单），同时把根订单标记为root_order，并把自身作为初始的child_order。
2. 递归成员：通过JOIN把当前层级的订单和它的子订单关联起来，把子订单的order_id加入到层级结构中，直到没有更多子订单为止。这里加了o.order_type = 'Auto_renewal'的条件，确保只统计自动续费类型的子订单，完全匹配你的业务场景。
3. 最终统计：分组统计每个root_order对应的child_order数量，因为锚点里包含了根订单本身，所以要减1得到真正的子订单总数。

测试数据验证 #

如果你需要测试，可以先插入示例数据：

CREATE TABLE orders (
    order_id VARCHAR(10) PRIMARY KEY,
    generated_by VARCHAR(10),
    order_type VARCHAR(20)
);

INSERT INTO orders VALUES
('X', NULL, 'Subscription'),
('Y', 'X', 'Auto_renewal'),
('Z', 'Y', 'Auto_renewal'),
('A', NULL, 'Subscription'),
('B', 'A', 'Auto_renewal');

执行上面的递归查询语句，就能得到你期望的结果。

内容的提问来源于stack exchange，提问作者APIS