查询，查找相关行的列的和

I have two tables like

我有两张桌子

1.GROUP_TABLE

1. group_table

Name  Under
--------------
G1    OWNED
G2    G1
G3    G2
G4    G1
G5    G2
G6    G4
G7    G2

STOCK_TABLE

group_name    stock
-------------------
G1               10
G2               20
G3               30
G4               10
G5               10
G6               10
G7               20

In grouptable there is groups belongs under another groups. if i will required stock of G1 then it will retrurn 110 (sum of all under groups related to G1). As: (G1)->(G2,G4)->(G5,G6,G7). same with G2 -80 because (G2)->(G3,G5,G7) and so on for G3,G4,G5,G6,G7.

在grouptable中，组属于另一个组。如果我需要G1的库存，那么它将retrurn 110(与G1相关的所有组的总和)。:(G1)- >(G2,G4)- >(G5、G6 G7)。G2 -80也一样，因为(G2)->(G3,G5,G7)，以此类推，G3,G4,G5,G6,G7。

I need result like

我需要结果,正如

TillGroup           Stock
--------           --------
G1                110
G2                80
G3                30
G4                20
G5                10
G6                10
G7                20

Please suggest me any sql query witch will provide me same result Thanks in advance.

请建议我任何sql查询女巫将提供我同样的结果提前谢谢。

2 个解决方案

#1

You'd use a recursive query for this:

你可以使用递归查询:

with tree as 
(
  select name as master_name, name as descendant_name, under from group_table
  union all
  select 
    parent.name as master_name, 
    child.descendant_name as descendant_name, 
    parent.under
  from group_table parent
  join tree child on child.under = parent.name
)
select tree.master_name, sum(stock_table.stock)
from tree
join stock_table on stock_table.group_name = tree.descendant_name
group by tree.master_name
order by tree.master_name;

See SQL fiddle http://www.sqlfiddle.com/#!3/4e557/26.

看到SQL小提琴http://www.sqlfiddle.com/ ! 3/4e557/26。

#2

Edit: Having re-read the question more carefully, my answer will not work as it does not traverse the childrens children.

编辑:仔细地重读这个问题，我的答案将不会起作用，因为它不会遍历孩子们的孩子。

select G.NAME, SUM(G1S.STOCK) + GS.STOCK
from GROUP_TABLE G -- consider this the parent
JOIN STOCK_TABLE GS ON GS.GROUP_NAME = G.NAME -- Get its stock number
JOIN GROUP_TABLE G1 ON G1.UNDER = G.NAME -- self-join to get all its children
JOIN STOCK_TABLE G1S ON G1S.GROUP_NAME = G1.NAME -- get the stock number for each of its children
GROUP BY G.NAME, GS.STOCK

Sorry, I haven't compiled this to check the syntax. There might be some small syntax errors.

对不起，我还没有编译它来检查语法。可能有一些小的语法错误。

#1