无法弄清楚如何汇总嵌套ID表

Question

我有一个像这样的表

LEVEL_01   LEVEL_02    LEVEL_03 
---        ---         ---
10010      10030       30010
10010      10040       30030
10010      10040       30040
10020      20010       20080
10020      10040       20080
10020      10040       20090
10021      20020       40000

和一张最后一个级别的表格

id         amount
---     -------------
30010      100.00
30030      190.00
30040      800.00
30040      700.00
40000      250.00
20080      320.00
20090      500.00

我需要的是显示三个级别的总和数据，分组/嵌套级别如：

10010->10030->30010
10010->10040->30030
10010->10030->30040
-------------------
10010=sum(30010)+sum(30030)+sum(30040)=(100.00)+(190.00)+(1500.00) = 1790.00
10030=sum(30010)+sum(30040)=(100.00)+(1500.00) = 1600.00
30040=sum(30040) = 1500.00
(one record for LEVEL_10 thru LEVEL_03 and their relative sum. 

不能使它起作用。任何帮助将不胜感激

Answer 1

您只需与一个加入一起完成总和，然后将结果与每个级别的结果结合，例如：

SELECT LEVEL_01, sum(amount) 
FROM table1 INNER JOIN table2 ON table1.level_03 = table2.id
GROUP BY LEVEL_01
UNION ALL
SELECT LEVEL_02, sum(amount) 
FROM table1 INNER JOIN table2 ON table1.level_03 = table2.id
GROUP BY LEVEL_02
UNION ALL
SELECT LEVEL_03, sum(amount) 
FROM table1 INNER JOIN table2 ON table1.level_03 = table2.id
GROUP BY LEVEL_03;