我有一个表“ TB_Population”,其中包含有关世界各地人口的一些记录。目前,我想计算特定行中每个标题的人口,并在该表中演示每个级别。
我的这张表包含以下数据:
ID TITLE PARENT_ID POPULATION
1 WORLD 10
2 AFRICA 1 5
3 ASIA 1 10
4 EUROPE 1 4
5 GERMANY 4 6
6 FRANCE 4 10
7 ITALY 4 4
8 JAPAN 3 6
9 MORROCO 2 1
10 SPAIN 4 9
11 INDIA 3 8
12 PORTUGAL 4 2
13 USA 14 10
14 AMERICA 1 10
15 NEWYORK 13 5
预期输出表应如下
ID TITLE POPULATION LEVEL
1 WORLD 100 1
2 AFRICA 6 2
3 ASIA 24 2
4 EUROPE 35 2
5 GERMANY 6 3
6 FRANCE 10 3
7 ITALY 4 3
8 JAPAN 6 3
9 MORROCO 1 3
10 SPAIN 9 3
11 INDIA 8 3
12 PORTUGAL 2 3
13 USA 15 3
14 AMERICA 25 2
15 NEWYORK 5 4
谢谢和最好的问候
我在这里看到的棘手部分是您希望标题的级别从“底部到顶部”,而人口从“顶部到底部”。例如,AMERICA的级别必须为2,这意味着必须从AMERICA-> WORLD来测量LEVEL,但是AMERICA的人口必须是25从AMERICA-> NEWYORK来衡量的总和。所以,我尝试了这个:
SELECT TOP_TO_BOTTOM.TITLE_ALIAS, TOP_TO_BOTTOM.TOTAL_POPULATION, BOTTOM_TO_TOP.MAX_LEVEL FROM
(SELECT TITLE_ALIAS, SUM(POPULATION) AS "TOTAL_POPULATION" FROM
(SELECT CONNECT_BY_ROOT TITLE AS "TITLE_ALIAS", POPULATION
FROM TB_POPULATION
CONNECT BY PRIOR ID = PARENT_ID)
GROUP BY TITLE_ALIAS) "TOP_TO_BOTTOM"
INNER JOIN
(SELECT TITLE_ALIAS, MAX(LEV) AS "MAX_LEVEL" FROM
(SELECT CONNECT_BY_ROOT TITLE AS "TITLE_ALIAS", LEVEL AS "LEV"
FROM TB_POPULATION
CONNECT BY PRIOR PARENT_ID = ID)
GROUP BY TITLE_ALIAS) "BOTTOM_TO_TOP"
ON
BOTTOM_TO_TOP.TITLE_ALIAS = TOP_TO_BOTTOM.TITLE_ALIAS
ORDER BY BOTTOM_TO_TOP.MAX_LEVEL;
您可以在此处查看仿真:https : //rextester.com/HFTIH47397。希望这对您有帮助
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句