sql - 在SQL语句中查找递归和

Question

我有两个表 A 和 B，例如:A (id, dep_id) 和 B (id, amount)

那些表里的数据是这样的

A                 B

id  dep_id        id   amount
--- -------       ---- --------
1   2             1    100    
2   3             2    200
3   NULL          3    300   
4   NULL          4    400

表 A 中的 id 列包含表 B 的 id。对于表 A 中给定的 id，可能有一个 dep_id 包含表 B 的 id。

需求是计算B中某分录及其所有依赖分录的金额之和。这必须在一个单独的 sql 查询中完成。我不能为此使用 PL/SQL block 。知道如何做到这一点。

例子:

sum(id=1) = 100(id=1,dep_id=2) + 200(id=2,dep_id=3) + 300(id=3) = 600

Answer 1

您可以使用CONNECT BY ROOT 建立一个依赖链接(hierarchical query)，然后聚合:

SQL> SELECT ID, SUM(amount)
  2    FROM (SELECT connect_by_root(a.id) ID, b.amount
  3            FROM a
  4            JOIN b ON a.id = b.id
  5           START WITH a.ID = 1
  6           CONNECT BY PRIOR a.dep_id = a.ID)
  7   GROUP BY ID;

        ID SUM(AMOUNT)
---------- -----------
         1         600

其他解决方案可用于类似但稍微复杂的架构(例如 id:1 需要 4xid:2，这需要 8xid:3 每个)在 this SQL quiz on plsqlchallenge 上.