数据如下:(ORDR为单位序号,NUM1为单位人数,FAR为上级单位序号)
ORDR NUM1 FAR
10 0
1001 5 10
1002 5 10
20 0
2001 5 20
200101 5 2001
---------测试脚本如下--------------------------
insert into AA select '10','0','' from dual;
insert into AA select'1001','5','10' from dual;
insert into AA select'1002','5','10' from dual;
insert into AA select'20','0','' from dual;
insert into AA select'2001','5','20' from dual;
insert into AA select '200101','5','2001' from dual; 想动态汇总下级单位人数,
比如只展示一级单位数据,需要得到如下数据:
ORDR NUM1 FAR
10 10
20 10
只展示二级单位数据,需要得到如下数据:
ORDR NUM1 FAR
10 10
1001 5 10
1002 5 10
20 10
2001 10 20
展示到三级单位数据,需得到如下数据:
ORDR NUM1 FAR
10 10
1001 5 10
1002 5 10
20 10
2001 10 20
200101 5 2001
ORDR NUM1 FAR
10 0
1001 5 10
1002 5 10
20 0
2001 5 20
200101 5 2001
---------测试脚本如下--------------------------
insert into AA select '10','0','' from dual;
insert into AA select'1001','5','10' from dual;
insert into AA select'1002','5','10' from dual;
insert into AA select'20','0','' from dual;
insert into AA select'2001','5','20' from dual;
insert into AA select '200101','5','2001' from dual; 想动态汇总下级单位人数,
比如只展示一级单位数据,需要得到如下数据:
ORDR NUM1 FAR
10 10
20 10
只展示二级单位数据,需要得到如下数据:
ORDR NUM1 FAR
10 10
1001 5 10
1002 5 10
20 10
2001 10 20
展示到三级单位数据,需得到如下数据:
ORDR NUM1 FAR
10 10
1001 5 10
1002 5 10
20 10
2001 10 20
200101 5 2001
select t.*
from AA t
where level = 1
start with far is null
connect by prior ORDR = FAR
select t.*
from AA t
where level = 1
or level = 2
start with far is null
connect by prior ORDR = FAR
select t.* from AA t start with far is null connect by prior ORDR = FAR
CONNECT BY PRIOR ordr=far
AND LEVEL<=3其中LEVEL表示级数
select t.*
from AA t
where level = 1
start with far is null
connect by prior ORDR = FAR
select t.*
from AA t
where level = 1
or level = 2
start with far is null
connect by prior ORDR = FAR
select t.* from AA t start with far is null connect by prior ORDR = FAR