省市区数据只有最末级有数据,需要一级一级向上累加的sql
数据格式如下,is_last_loc为Y时表示最末级,只有在最末级有数据,父层级都是null需要累加:

location_id location_name population parent_id is_last_loc
1 广东省 null null N
2 广州市 null 1 N
3 越秀区 123123 2 Y
4 天河区 345345 2 Y

想要的结果如下:

location_name sum_population
广东省 468468
广州市 468468
越秀区 123123
天河区 345345

层级最多有5级, 数据大约有300条, 恳请牛人指点...


------解决方案--------------------
with tab as
 (select 1 location_id,
'广东省' location_name,
null population,
null parent_id,
'N' is_last_loc
from dual
union
select 2 location_id,
'广州市' location_name,
null population,
1 parent_id,
'N' is_last_loc
from dual
union
select 3 location_id,
'越秀区' location_name,
123123 population,
2 parent_id,
'Y' is_last_loc
from dual
union
select 4 location_id,
'天河区' location_name,
345345 population,
2 parent_id,
'Y' is_last_loc
from dual)
select t1.*,
(select sum(t2.population)
From tab t2
connect by t2.parent_id = prior t2.location_id
start with t2.location_id = t1.location_id)
From tab t1;
------解决方案--------------------
才300条啊,开子查询

select t.*,(select count(*) from t connect by parent_id = prior location_id start with location_id = t.location_id ) from table t
------解决方案--------------------
实测数据：

SQL code

CREATE TABLE T154
(
    LocationID NUMBER(2),
    LocationName VARCHAR2(20),
    Population NUMBER(10),
    ParentID NUMBER(2),
    IsLastLocation CHAR(1)
);

INSERT INTO T154 VALUES(1, '广东省', null, null, 'N');
INSERT INTO T154 VALUES(2, '广州市', null, 1, 'N');
INSERT INTO T154 VALUES(3, '越秀区', 10, 2, 'Y');
INSERT INTO T154 VALUES(4, '天河区', 20, 2, 'Y');
INSERT INTO T154 VALUES(5, '深圳', null, 1, 'N');
INSERT INTO T154 VALUES(6, '罗湖区', 30, 5, 'Y');
INSERT INTO T154 VALUES(7, '天台区', 40, 5, 'Y');

INSERT INTO T154 VALUES(8, '辽宁省', null, null, 'N');
INSERT INTO T154 VALUES(9, '大连市', null, 8, 'N');
INSERT INTO T154 VALUES(10, '中山区', 50, 9, 'Y');
INSERT INTO T154 VALUES(11, '西岗区', 60, 9, 'Y');
INSERT INTO T154 VALUES(12, '营口市', null, 8, 'N');
INSERT INTO T154 VALUES(13, '营口A区', 70, 12, 'Y');
INSERT INTO T154 VALUES(14, '营口B区', 80, 12, 'Y');