示例数据:
A X 0
B X 0
C X 0
A1 A 1
A2 A 1
A11 A1 1
A12 A1 1
A21 A2 1
得到B的视图:
A1 A A
A2 A A
A11 A1 A
A12 A1 A
A21 A2 A
如何创建B这样的视图?SQL语句如何写?请指教
...全文
8316打赏收藏
如何创建这样一个视图?
表:A(ID ParentID Type) Type为0 和 1 表示单位和部门,我想把部门全部列出来如下:RootID为部门所属的单位。 B(ID ParentID RootID) 示例数据: A X 0 B X 0 C X 0 A1 A 1 A2 A 1 A11 A1 1 A12 A1 1 A21 A2 1 得到B的视图: A1 A A A2 A A A11 A1 A A12 A1 A A21 A2 A 如何创建B这样的视图?SQL语句如何写?请指教
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;
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);