高分求助一简单问题,描述特详细,分不够再加
-----------测试数据--------------
create table test111(父层 varchar2(20),父层代码 varchar2(20),子层 varchar2(20),子层代码 varchar2(20))
insert into test111(父层,父层代码,子层,子层代码)VALUES('L500957','L02041','UE24205','L02021')
insert into test111(父层,父层代码,子层,子层代码)VALUES('L500957','L02041','7155031A','DUMMY')
insert into test111(父层,父层代码,子层,子层代码)VALUES('UE24205','L12021','7155031A','DUMMY')
insert into test111(父层,父层代码,子层,子层代码)VALUES('UE24205','L02021','7155031A','DUMMY')
insert into test111(父层,父层代码,子层,子层代码)VALUES('L500958','L12041','UE24205','L02021')
insert into test111(父层,父层代码,子层,子层代码)VALUES('L500958','L12041','UE24205','L12021')
insert into test111(父层,父层代码,子层,子层代码)VALUES('L500958','L12041','7155031A','DUMMY')
-----------测试数据--------------
我的限制条件是:
1、父层代码前两位为‘L1’时,子层代码的前两位可以为‘L0’或‘L1’
2、父层代码前两位为‘L0’时,子层代码的前两位只能为‘L0’
3、子层代码为‘DUMMY’时是最底层的子层,不受1、2两个条件限制
SELECT LEVEL AS LV,父层,父层代码,子层,子层代码 FROM WEBSKY.TEST111
start with 父层 like 'L500%'
connect by prior 子层=父层
以上是展开构成的语句,得到的结果为:
LV 父层 父层代码 子层 子层代码
---------------------------------------------
1 L500957 L02041 UE24205 L02021
2 UE24205 L12021 7155031A DUMMY---(错误,L500957下没有子层代码为L12021的子层)
2 UE24205 L02021 7155031A DUMMY
1 L500957 L02041 7155031A DUMMY
1 L500958 L12041 UE24205 L12021
2 UE24205 L12021 7155031A DUMMY
2 UE24205 L02021 7155031A DUMMY
1 L500958 L12041 UE24205 L02021
2 UE24205 L12021 7155031A DUMMY--(重复数据)
2 UE24205 L02021 7155031A DUMMY--(重复数据)
1 L500958 L12041 7155031A DUMMY
希望得到的结果为:
LV 父层 父层代码 子层 子层代码
---------------------------------------------
1 L500957 L02041 UE24205 L02021
2 UE24205 L02021 7155031A DUMMY
1 L500957 L02041 7155031A DUMMY
1 L500958 L12041 UE24205 L12021
2 UE24205 L12021 7155031A DUMMY
2 UE24205 L02021 7155031A DUMMY
1 L500958 L12041 UE24205 L02021
1 L500958 L12041 7155031A DUMMY
请问语句应该怎么写,谢谢各位英雄了。。。