mysql - 如何在 SQL 中递归求和所有子子树？

Question

美好的一天，我已经为这个问题烦恼了一段时间><"

我的树结构中有 4 个类别。

tenant_category_transaction_view:

我想要每个类别的所有子项“sumSubtotal”的总和

类似这样的事情:

我已经非常接近了...但是有一些东西我不明白><"

with recursive cte (sumSubtotal, sumQuantity, id, idParentCategory, treeSum, depth) as (

        select  root.sumSubtotal, -- STEP 1
                root.sumQuantity, 
                root.id, 
                root.idParentCategory, 
                root.sumSubtotal as treeSum,
                0 as depth
        from    tenant_category_transaction_view as root

        union all -- LOOP THROUGH ALL ROOT ROWS AND ADD ROWS TO THE CTE WITH THE INNER JOIN

        select  child.sumSubtotal, -- STEP 3
                child.sumQuantity, 
                child.id, 
                child.idParentCategory, 
                (cte.treeSum + child.sumSubtotal) AS treeSum,
                (cte.depth + 1) AS depth
        from    tenant_category_transaction_view AS child

        inner join cte on child.idParentCategory = cte.id -- STEP 2
)
select sumSubtotal, sumQuantity, id, idParentCategory, treeSum, depth -- STEP 4
from cte

上述查询的结果:

看来我生成了正确的 treeSum，但只有一个分支颠倒了

您能帮我一下吗？

感谢您的宝贵时间:)

Answer 1

我已经更新了 fiddle 以包含问题中提供的确切架构/数据，包括空问题。它还包括我建议的更改的示例。

该解决方案基本上采用给定的数据并在内部进行转换(在 CTE 术语 nodes 中)，以便 2 个顶级类别行链接到 id 0 的公共(public)行，以便我提供的原始逻辑可以是用于将其视为一个类别的分层列表。

首先，我们递归地查找所有分支列表。每个分支由相应的根标识。然后，我们聚合每个根/分支的节点数量。

The fiddle

WITH RECURSIVE nodes AS (
         SELECT id, COALESCE(idParentCategory, 0) AS idParentCategory
              , sumSubtotal, sumQuantity
           FROM tenant_category_transaction_view
          UNION
         SELECT 0, null, 0, 0
     )
   , cte AS (
        SELECT t.*, t.id AS root
             , idParentCategory AS idParentCategory0
             , sumSubtotal      AS sumSubtotal0
             , sumQuantity      AS sumQuantity0
          FROM nodes AS t
         UNION ALL
        SELECT t.* , t0.root
             , t0.idParentCategory0
             , t0.sumSubtotal0
             , t0.sumQuantity0
          FROM cte AS t0
          JOIN nodes AS t
            ON t.idParentCategory = t0.id
     )
SELECT root
     , MIN(idParentCategory0)   AS idParentCategory
     , MIN(sumSubtotal0)        AS sumSubtotal
     , MIN(sumQuantity0)        AS sumQuantity
     , SUM(t1.sumSubtotal)      AS total
  FROM cte AS t1
 GROUP BY root
 ORDER BY root
;

结果:

<表类=“s-表”><标题>根idParentCategory总和小计总数量总计 <正文>0空009890109800989800402019054301706540140

设置:

CREATE TABLE tenant_category_transaction_view (
    id               int primary key
  , idParentCategory int
  , sumSubtotal      int
  , sumQuantity      int
);

INSERT INTO tenant_category_transaction_view VALUES
    (1, null, 9800, 98)
  , (4, null,   20,  1)
  , (5,    4,   30,  1)
  , (6,    5,   40,  1)
;

以下对原始表格和数据进行了建议的轻微调整。添加顶行(例如 id 99)，并让 id 1 和 4 的行引用parent = 99 的行，而不是使用 id 1 和 4 的行的 2 个顶部空父引用。

WITH RECURSIVE cte AS (
        SELECT t.*, t.id AS root
          FROM tenant_category_transaction_view AS t
         UNION ALL
        SELECT t.*, t0.root
          FROM cte AS t0
          JOIN tenant_category_transaction_view AS t
            ON t.idParentCategory = t0.id
     )
SELECT root
     , MIN(t2.idParentCategory) AS idParentCategory
     , MIN(t2.sumSubtotal)      AS sumSubtotal
     , MIN(t2.sumQuantity)      AS sumQuantity
     , SUM(t1.sumSubtotal)      AS total
  FROM cte AS t1
  JOIN tenant_category_transaction_view AS t2
    ON t1.root = t2.id
 GROUP BY root
 ORDER BY root
;

结果:

<表类=“s-表”><标题>根idParentCategory总和小计总数量总计 <正文>99空00989019998009898004992019054301706540140

此外，由于功能依赖，可以将其写入基于 t2.id(主键)的聚合，从而允许稍微简化。

WITH RECURSIVE cte AS (
        SELECT t.*, t.id AS root
          FROM tenant_category_transaction_view AS t
         UNION ALL
        SELECT t.*, t0.root
          FROM cte AS t0
          JOIN tenant_category_transaction_view AS t
            ON t.idParentCategory = t0.id
     )
SELECT t2.id
     , t2.idParentCategory
     , t2.sumSubtotal
     , t2.sumQuantity
     , SUM(t1.sumSubtotal)      AS total
  FROM cte AS t1
  JOIN tenant_category_transaction_view AS t2
    ON t1.root = t2.id
 GROUP BY t2.id
 ORDER BY t2.id
;

最后，我们可以通过在递归逻辑中携带其他根值来删除最后一个 JOIN:

WITH RECURSIVE cte AS (
        SELECT t.*, t.id AS root
             , idParentCategory AS idParentCategory0
             , sumSubtotal      AS sumSubtotal0
             , sumQuantity      AS sumQuantity0
          FROM tenant_category_transaction_view AS t
         UNION ALL
        SELECT t.* , t0.root
             , t0.idParentCategory0
             , t0.sumSubtotal0
             , t0.sumQuantity0
          FROM cte AS t0
          JOIN tenant_category_transaction_view AS t
            ON t.idParentCategory = t0.id
     )
SELECT root
     , MIN(idParentCategory0)   AS idParentCategory
     , MIN(sumSubtotal0)        AS sumSubtotal
     , MIN(sumQuantity0)        AS sumQuantity
     , SUM(t1.sumSubtotal)      AS total
  FROM cte AS t1
 GROUP BY root
 ORDER BY root
;

设置:

DROP TABLE IF EXISTS tenant_category_transaction_view;
CREATE TABLE tenant_category_transaction_view (
    id               int primary key
  , idParentCategory int
  , sumSubtotal      int
  , sumQuantity      int
);

INSERT INTO tenant_category_transaction_view VALUES
    (99, null,    0,  0)
  , ( 1,   99, 9800, 98)
  , ( 4,   99,   20,  1)
  , ( 5,    4,   30,  1)
  , ( 6,    5,   40,  1)
;