查找递归总和的SQL语句 [英] Finding recursive sum in SQL statement
问题描述
我有两个表A和类似B:
A(ID,dep_id)
和 B(ID,数量)
这些表中都是这样的数据。
A B
ID dep_id ID量
--- ------- ---- --------
1 2 1 100
2 3 2 200
3空3 300
4空4 400
在 ID
表中的列持有 ID
为表B.对于给定的表A ID
,有可能是一个 dep_id
持有 ID
表B的
要求是计算在乙的条目的数量及其所有从属项的总和。这必须在一个单独的SQL查询完成。我不能使用PL / SQL块的。任何想法如何做到这一点。
例如:
的总和(ID = 1)= 100(ID = 1,dep_id = 2)+ 200(ID = 2,dep_id = 3)+ 300(ID = 3)= 600
您可以使用 CONNECT BY ROOT
来建立相关的链接(的分层查询),然后汇总:
SQL> SELECT ID,SUM(金额)
2 FROM(SELECT CONNECT_BY_ROOT(a.id)ID,b.amount
3从
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
需要4X ID:2
,这就需要8X ID:3
每个)<一个href="http://www.plsqlchallenge.com/pls/apex/f?p=10000:659:9203208106513%3a%3aNO%3a%3aP659_COMP_EVENT_ID,P659_QUESTION_ID,P659_QUIZ_ID:129614,7584,&cs=1210D1D5FB219A8E265ECC8DBD780484A#ltq"相对=nofollow>这个SQL测验上plsqlchallenge 。
I've two tables A and B like:
A (id, dep_id)
and B (id, amount)
The data in those tables are like this
A B
id dep_id id amount
--- ------- ---- --------
1 2 1 100
2 3 2 200
3 NULL 3 300
4 NULL 4 400
The id
column in table A holds id
for table B. For a given id
in table A, there might be a dep_id
which holds id
of table B.
The requirement is to calculate the sum of amount of an entry in B and all of its dependent entries. This has to be done in one single sql query. I can't use PL/SQL block for that. Any idea how to do that.
Example:
sum(id=1) = 100(id=1,dep_id=2) + 200(id=2,dep_id=3) + 300(id=3) = 600
You can use CONNECT BY ROOT
to build a link of dependency (hierarchical query), then aggregate:
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
Additional solutions are available on a similar but slightly more complex schema (for example id:1
needs 4xid:2
, which needs 8xid:3
each) on this SQL quiz on plsqlchallenge.
这篇关于查找递归总和的SQL语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!