如何使用父子关系执行同一表的求和

Question

我有一个表，其中存储了家庭每月的支出。在这个表中有一个父子关系。我想从这里得到这个家庭的总支出。那么，我该如何更新TOTAL_AMOUNT列或执行该操作呢？

为了查询，我给出了一个伪表并插入了脚本。

CREATE TABLE PARENT_CHILD
(
ID             NUMBER (10),
PARENT_ID      NUMBER (10),
OWN_AMOUNT     NUMBER (20),
TOTAL_AMOUNT   NUMBER (20)
);
INSERT INTO PARENT_CHILD VALUES (1, NULL, 100000, NULL);
INSERT INTO PARENT_CHILD VALUES (2, NULL, 15000, NULL);
INSERT INTO PARENT_CHILD VALUES (3, NULL, 10000, NULL);
INSERT INTO PARENT_CHILD VALUES (4, 3, 2000, NULL);
INSERT INTO PARENT_CHILD VALUES (5, NULL, 100000, NULL);
INSERT INTO PARENT_CHILD VALUES (6, 5, 25000, NULL);
INSERT INTO PARENT_CHILD VALUES (7, 6, 15000, NULL);
INSERT INTO PARENT_CHILD VALUES (8, 5, 30000, NULL);
INSERT INTO PARENT_CHILD VALUES (9, 5, 20000, NULL);
INSERT INTO PARENT_CHILD VALUES (10, 5, 8000, NULL);
INSERT INTO PARENT_CHILD VALUES (11, 10, 2000, NULL);

例如，我想更新如下表：

ID
1		2		3		4		5		6		7		8		9		10		11

由于存在的不仅仅是父子关系(还有孙关系，可能还有更深层次的关系)，您需要一个递归CTE来正确地说出谁是并更新源表。

类似这样的东西：

SQL> update parent_child pc set
2    pc.total_amount =
3      (with
4          children (parent, child) as
5            (select parent_id, id
6             from parent_child
7             union all
8             select d.parent, a.id
9             from children d join parent_child a on d.child = a.parent_id
10            )
11        select
12          sum(a.own_amount) amount
13        from children c join parent_child a on a.id = c.child
14        where nvl(c.parent, c.child) = pc.id
15        group by nvl(c.parent, c.child)
16       );
11 rows updated.

结果：

SQL> select * from parent_child order by id;
ID  PARENT_ID OWN_AMOUNT TOTAL_AMOUNT
---------- ---------- ---------- ------------
1                100000       100000
2                 15000        15000
3                 10000        12000
4          3       2000         2000
5                100000       200000
6          5      25000        40000
7          6      15000        15000
8          5      30000        30000
9          5      20000        20000
10          5       8000        10000
11         10       2000         2000
11 rows selected.
SQL>

您可以使用相关的分层查询：

UPDATE parent_child p
SET total_amount = (SELECT SUM(own_amount)
FROM   parent_child s
START WITH s.ROWID = p.ROWID
CONNECT BY PRIOR id = parent_id);

对于样本数据，输出：

>td style="text-align:right；">2000>td style="ext-align:right；">20000>td style="ext-align:right；">40000<15000>

ID
1	null		2	空		3	null	1000	12000
4	3	2000
5	空	10000
6	5	25000
7	6	15000
8	5	30000
9		10		11