省市区数据只有最末级有数据,需要一级一级向上累加的sql 本帖最后由 willishz 于 2012-03-13 15:03:23 编辑 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 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 实测数据: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');实测结果: oracle 11g R2 安装遇到找不到文件的问题 求助! 数据批量导入问题 请问2个查询语句如何写? 求存储过程的例子 oracle定时job问题,求助 关于JOB自动运行,超级郁闷 请教:为什么我安装oracle9i服务器后没有监听服务 高分请求赐教,很简单的(嫌不够,您只管说,我加) 请问哪儿有免费的ORACLE8或ORACLE9的下载啊? 关于distinct求不重复记录的问题 求一条sql语句 ASP.NET(C#)中如何将string类型存储为CLOB???
(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;
(
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');
实测结果: