SQL获取父节点的所有子节点，并向父节点添加子节点的值

Question

假设我有一个这样的表。

ID  Parent  Value
1   NULL    1000
2   1       1000
3   2       1000
4   2       1000
5   2       1000
6   2       1000
7   2       1000
8   1       1000
9   8       1000
10  8       1000
11  8       1000

我想递归地添加给定id的每个子值。正确的输出是。

ID  Parent  Value
1   NULL    11000
2   1       6000
3   2       1000
4   2       1000
5   2       1000
6   2       1000
7   2       1000
8   1       4000
9   8       1000
10  8       1000
11  8       1000

只有一个"top"父节点，它的parent值为"Null"。我是非常新的SQL，所以任何类型的帮助将不胜感激。如果有帮助的话，我正在使用Oracle 11。

Answer 1

可以，您可以使用CONNECT_BY_ROOT运算符。

基本上，"START WITH"每一行，对每个根的子节点求和，然后按根分组。这样的:

with test_data (id, parent, value) as ( 
SELECT 1,   NULL,    1000 FROM DUAL UNION ALL
SELECT 2,   1,       1000 FROM DUAL UNION ALL
SELECT 3,   2,       1000 FROM DUAL UNION ALL
SELECT 4,   2,       1000 FROM DUAL UNION ALL
SELECT 5,   2,       1000 FROM DUAL UNION ALL
SELECT 6,   2,       1000 FROM DUAL UNION ALL
SELECT 7,   2,       1000 FROM DUAL UNION ALL
SELECT 8,   1,       1000 FROM DUAL UNION ALL
SELECT 9,   8,       1000 FROM DUAL UNION ALL
SELECT 10,  8,       1000 FROM DUAL UNION ALL
SELECT 11,  8,       1000 FROM DUAL)
SELECT root_id id, root_parent parent, sum(value) value
FROM ( 
SELECT connect_by_root(id) root_id, connect_by_root(parent) root_parent, value 
FROM test_data td
connect by parent = prior id
-- notice there is no "start with" clause
)
group by root_id, root_parent
order by root_id