部门编码的问题应该通过编码知道所属的部门示例数据: A X 0 B X 0 C X 0 A1 A 1 B1 B 1 A11 A1 1 A12 A1 1 B11 B1 1其中A,B设置长度相同 这样就一目了然,而且方便处理
create table TableA ( id varchar2(5), pid varchar2(5), type char(1)) /insert into TableA values ('A','X',0); insert into TableA values ('B','X',0); insert into TableA values ('C','X',0); insert into TableA values ('CC','C',0); insert into TableA values ('A1','A',1); insert into TableA values ('A2','A',1); insert into TableA values ('B1','B',1); insert into TableA values ('A11','A1',1); insert into TableA values ('A21','A2',1); insert into TableA values ('A22','A2',1); insert into TableA values ('C1','C',1); insert into TableA values ('C11','C1',1); insert into TableA values ('C12','C1',1); commit;select A.id, A.pid, B.id as Rootid from TableA A, (select id from TableA B1 where B1.Type='0' and not exists(select 1 from TableA B2 where B2.id=B1.pid)) B where A.type='1' and exists(select 1 from TableA C connect by prior id=pid start with C.id=B.id);前提条件表中的数据除了最高层的单位外,其他各单位、部门的pid在表中都能找到对应记录。
不好意思,写错了。应该是:select A.id, A.pid, B.id as Rootid from TableA A, (select id from TableA B1 where B1.Type='0' and not exists(select 1 from TableA B2 where B2.id=B1.pid)) B where A.type='1' and exists(select 1 from TableA C where C.ID=A.ID connect by prior id=pid start with C.id=B.id) order by 1;
A(单位id,单位名称);
B(部门id,部门名称);
使用时,C(单位id,部门id,...)。要么使用数据库树型结构存放。
A X 0
B X 0
C X 0
A1 A 1
B1 B 1
A11 A1 1
A12 A1 1
B11 B1 1其中A,B设置长度相同
这样就一目了然,而且方便处理
id varchar2(5),
pid varchar2(5),
type char(1))
/insert into TableA values ('A','X',0);
insert into TableA values ('B','X',0);
insert into TableA values ('C','X',0);
insert into TableA values ('CC','C',0);
insert into TableA values ('A1','A',1);
insert into TableA values ('A2','A',1);
insert into TableA values ('B1','B',1);
insert into TableA values ('A11','A1',1);
insert into TableA values ('A21','A2',1);
insert into TableA values ('A22','A2',1);
insert into TableA values ('C1','C',1);
insert into TableA values ('C11','C1',1);
insert into TableA values ('C12','C1',1);
commit;select A.id, A.pid, B.id as Rootid
from TableA A,
(select id from TableA B1
where B1.Type='0'
and not exists(select 1 from TableA B2 where B2.id=B1.pid)) B
where A.type='1'
and exists(select 1
from TableA C
connect by prior id=pid
start with C.id=B.id);前提条件表中的数据除了最高层的单位外,其他各单位、部门的pid在表中都能找到对应记录。
from TableA A,
(select id from TableA B1
where B1.Type='0'
and not exists(select 1 from TableA B2 where B2.id=B1.pid)) B
where A.type='1'
and exists(select 1
from TableA C
where C.ID=A.ID
connect by prior id=pid
start with C.id=B.id)
order by 1;
from a
start with id='A'
connect by parentid=prior id)
where Type='1';试试吧,还没测试过