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

p4tfgftt 于 2022-10-04 发布在 Oracle

关注(0)|答案(2)|浏览(146)

我有一张表，里面存放着一个家庭每月的支出。在此表中存在父子关系。从这里我想要得到家庭的总支出。那么，我如何更新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|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

提前感谢。

oracle

来源：https://stackoverflow.com/questions/73850180/how-to-perform-sum-from-same-table-using-parent-child-relation

2条答案

按热度按时间

jdzmm42g1#

由于不仅有父子关系(但也有孙子关系，可能还有更深的关系)，您需要一个递归CTE来正确地说出Who is Who并更新源表。

大概是这样的：

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>

赞(0）回复(0）举报 2022-10-04

icnyk63a2#

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

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

对于样本数据，它输出：

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

fiddle

赞(0）回复(0）举报 2022-10-04

我来回答

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

2条答案

相关问题

热门标签

最新问答