数据如下:(ORDR为单位序号,NUM1为单位人数,FAR为上级单位序号) create table AA ( ORDR int, NUM1 int, FAR int ) 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; //展示一级 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
放到游标里loop就可以了啊
create table AA
(
ORDR int,
NUM1 int,
FAR int
)
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; //展示一级
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