如何在SQL中以树状结构汇总从子级到父级的数据？

Question

我有一个查询，用于选择树状结构中每个部门的金额。我想在各自的父母上显示孩子的总和。

是否可以在不使用游标的情况下将其归档在查询中？

以下是要汇总的数据的结果集。完整示例也可以在sqlfiddle上找到。

结果 ：

| DEPARTMENT_ID | PARENT_DEP_ID |     DEPARTMENT |          AMOUNT |
|---------------|---------------|----------------|-----------------|
|             1 |             0 |              1 |               0 |
|             7 |             1 |             11 |               0 |
|            34 |             7 |            111 |               0 |
|           120 |            34 |           1111 |               0 |
|           402 |           120 |         111101 |               0 |
|           651 |           402 | 11110101/10000 | 227470.72339635 |
|           651 |           402 | 11110101/10000 |  52255.99610869 |
|           651 |           402 | 11110101/10000 |   4437.15281795 |
|           651 |           402 | 11110101/10000 |   4552.70289465 |
|           651 |           402 | 11110101/10000 |   8510.61790448 |
|           651 |           402 | 11110101/10000 |         8266.08 |
|           651 |           402 | 11110101/10000 |         9968.16 |
|           651 |           402 | 11110101/10000 |          242.58 |
|           403 |           120 |         111102 |               0 | <= this is where i
|           652 |           403 | 11110201/10005 |  120384.7842412 |    want to have my
|           652 |           403 | 11110201/10005 | 488733.59476206 |    sum from the 
|           652 |           403 | 11110201/10005 |    2318.6573888 |    child items
|           652 |           403 | 11110201/10005 |  23690.22829273 |
|           652 |           403 | 11110201/10005 | 38321.261680815 |
|           652 |           403 | 11110201/10005 |         6199.56 |
|           652 |           403 | 11110201/10005 |         7476.12 |
|           652 |           403 | 11110201/10005 |          161.92 |

Answer 1

这将获得完整的报告

SELECT t1.DEPARTMENT_ID
     , t1.PARENT_DEP_ID
     , t1.DEPARTMENT   
     , Sum(t2.Amount) Amount
FROM   TREE_DATA t1
       INNER JOIN TREE_DATA t2 
       ON t1.DEPARTMENT = SUBSTR(t2.DEPARTMENT, 1, LENGTH(t1.DEPARTMENT))
WHERE  t1.Amount = 0
GROUP BY t1.DEPARTMENT_ID, t1.PARENT_DEP_ID, t1.DEPARTMENT

UNION ALL

SELECT DEPARTMENT_ID
     , PARENT_DEP_ID
     , DEPARTMENT   
     , Amount
FROM   TREE_DATA
WHERE  Amount > 0
ORDER BY DEPARTMENT

第一个查询通过修改不包含金额的分部的部门名称结构来获得滚动总和，第二个查询获得叶子。
第一个查询无法显示叶子，因为它们将被分组。我还没有找到任何列组合来获得相同的顺序，叶子似乎是无序的。

SQLFiddle演示

我尝试编写一个递归CTE，但是不可能有聚合函数，例如SUM在其中。