自引用表聚合

Question

我看到了很多关于如何递归查询自引用表的问题/答案，但是我很难将我找到的答案应用于每个父级、祖父级等的聚合，而不管该项在层次结构中位于何处。

MyTable
-----------
Id
Amount
ParentId

数据：

Id    Amount      Parent Id
 1      100          NULL
 2      50            1
 3      50            1
 4      25            2
 5      10            4

如果我运行这个查询但不进行过滤，并且对金额进行求和，结果将是：

Id   SumAmount
 1       235
 2        85
 3        50
 4        35
 5        10

换句话说，我想查看MyTable中每个项目及其所有子项的总金额。

- Phil Sandler

Answer 1

像这样的东西？

    WITH temp (ID, ParentID, TotalAmount)
AS
(
    SELECT ID, ParentID, Amount
    FROM MyTable
    WHERE NOT EXISTS (SELECT * FROM MyTable cc WHERE cc.ParentID = MyTable.ID)

    UNION ALL

    SELECT MyTable.ID, MyTable.ParentID, TotalAmount + MyTable.Amount
    FROM MyTable
    INNER JOIN temp ON MyTable.ID = temp.ParentID
)

SELECT ID, SUM(TotalAmount) FROM
    (SELECT ID, TotalAmount - (SELECT Amount FROM MyTable M WHERE M.ID = temp.ID) TotalAmount
     FROM temp
     UNION ALL
     SELECT ID, Amount AS TotalAmount FROM MyTable) X
GROUP BY ID

根据下面的评论修改了查询，现在一切正常。

- Szymon

---

我认为 TotalAmount + MyTable.Amount 是错误的，因为它在 SUM(TotalAmount) 下面被求和了，但这是一个很好的递归方法。另外，应该只有 MyTable.Amount。顺便说一下，加1。 - Steve

如果从根节点到叶子节点有多条分支，那么就会出现这种情况。因为第一个查询是从叶子节点向上到根节点进行的，所以可能会为同一父节点返回多条记录（每个分支返回一条）。 - Szymon

不错，你是对的。我一开始以为他们想要不同的结果。我重新看了问题，我认为他们确实想要你所说的方式。 - Steve

@RomanPekar 你说得很对！谢谢！我已经更改了查询，但是不可否认，你的解决方案更加优雅，应该被接受。 - Szymon

Answer 2

with cte as (
    select t.Id, t.Amount, t.Id as [Parent Id]
    from Table1 as t

    union all

    select c.Id, t.Amount, t.Id as [Parent Id]
    from cte as c
        inner join Table1 as t on t.[Parent Id] = c.[Parent Id]
)

select Id, sum(Amount) as Amount
from cte
group by Id

sql fiddle demo

- Roman Pekar

Answer 3

WITH RECURSIVE linked_transactions (id, parent_id, amount) AS (
  SELECT id, parent_id, amount
  FROM transactions
  WHERE id = $transaction_id
  UNION ALL
  SELECT t.id, t.parent_id, t.amount
  FROM transactions t
  JOIN linked_transactions lt ON t.parent_id = lt.id
)
SELECT SUM(amount) AS sum
FROM linked_transactions;

- Ishan Khandelwal

---

请记住，Stack Overflow 不仅旨在解决当前的问题，还要帮助未来的读者找到类似问题的解决方案，这需要理解底层代码。对于我们社区中的初学者和不熟悉语法的成员来说，这尤其重要。鉴于此，您能否编辑您的答案，包括您正在做什么的解释以及为什么您认为这是最好的方法？ - Jeremy Caney