如何从孩子到父母总结SQL中树状结构的数据？ [英] How can I sum up data in tree-like structure in SQL from children to parent?

问题描述

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

I have a query for selecting amounts per department in a tree-like structure. I want to display the sum amount of the children on their respective parent.

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

Is it possible to archive this in a query without using a cursor?

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

Below is a resultset of the data to sum up. A full sample can also be found on sqlfiddle.

结果 ：

Results:

| 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 |

推荐答案

这将获得完整的报告

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

第一个查询通过破解没有金额的分部的部门名称，第二个获得叶子。

第一个查询无法显示叶子，因为它们将被分组。
我没有找到任何列组合来获得相同的顺序，叶子似乎是无序的。

The first query get the rolling sum by hacking the structure of the Department name for the oens without the amounts, the second one get the leafs.
The first query cannot show the leafs, as they will get grouped. I haven't found any column combination to get the same order, the leafs seems to be unordered.

SQLFiddle 演示

我尝试编写递归 CTE ，但是不可能具有聚合功能，例如其中包含 SUM 。

I've tried to write a recursive CTE, but it's not possible to have aggregate function, such as SUM in it.

这篇关于如何从孩子到父母总结SQL中树状结构的数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！