17,081
社区成员
发帖
与我相关
我的任务
分享
SQL> select * from depart;
R
--------------------------------------------------------------------------------
中国-广东-广州
中国-广东-深圳
中国-广东-潮州
中国-香港
中国-香港-中环
中国-台湾
已选择6行。
SQL> select distinct * from (select sys_connect_by_path(dname, ' ')n
2 from (select substr(dt.r,
3 instr('-' || dt.r, '-', 1, t1.rn),
4 instr('-' || dt.r || '-', '-', 1, t1.rn + 1) -
5 instr('-' || dt.r, '-', 1, t1.rn) - 1) dname,
6 t1.rn,
7 dt.rn drn
8 from (select rownum rn
9 from dual
10 connect by rownum <=
11 (select max(length(r) - length(replace(r, '-', '')) + 1)
12 from depart t)) t1,
13 (select r, rownum rn from depart) dt) vw where dname is not null
14 start with rn = 1
15 connect by rn = prior rn + 1
16 and drn = prior drn);
N
--------------------------------------------------------------------------------
中国
中国 香港 中环
中国 广东
中国 广东 广州
中国 香港
中国 广东 深圳
中国 广东 潮州
中国 台湾
已选择8行。
select sys_connect_by_path(dname, ' ')
from (select substr(dt.r,
instr('-' || dt.r, '-', 1, t1.rn),
instr('-' || dt.r || '-', '-', 1, t1.rn + 1) -
instr('-' || dt.r, '-', 1, t1.rn) - 1) dname,
t1.rn,
dt.rn drn
from (select rownum rn
from dual
connect by rownum <=
(select max(length(r) - length(replace(r, '-', '')) + 1)
from depart t)) t1,
(select r, rownum rn from depart) dt) vw where dname is not null
start with rn = 1
connect by rn = prior rn + 1
and drn = prior drn
create table test11 (l1l2l3 varchar2(50))
insert into test11
select '中国-广东-广州' from dual
union
select '中国-广东-深圳' from dual
union
select '中国-广东-潮州' from dual
union
select '中国-香港' from dual
union
select '中国-香港-中环' from dual
union
select '中国-台湾' from dual
commit;
select l1l2l3,substr(l1l2l3,0,instr(l1l2l3,'-',1)-1) l1,
case when substr(l1l2l3,instr(l1l2l3,'-',1)+1,instr(l1l2l3,'-',1,2)-instr(l1l2l3,'-',1,1)-1) is not null then
substr(l1l2l3,instr(l1l2l3,'-',1)+1,instr(l1l2l3,'-',1,2)-instr(l1l2l3,'-',1,1)-1) else
substr(l1l2l3,instr(l1l2l3,'-',1)+1,length(l1l2l3)-instr(l1l2l3,'-',1)+1) end l2,
substr(l1l2l3,instr(l1l2l3,substr(l1l2l3,instr(l1l2l3,'-',1)+1,instr(l1l2l3,'-',1,2)-instr(l1l2l3,'-',1,1)-1),1)+1+1+1,
length(l1l2l3)-instr(l1l2l3,substr(l1l2l3,instr(l1l2l3,'-',1)+1,instr(l1l2l3,'-',1,2)-instr(l1l2l3,'-',1,1)-1),1)) l3
from test11
create table test1(l1 varchar2(20),l2 varchar2(20),l3 varchar2(20))
insert into test1(l1,l2,l3)
select '中国','广东','广州' from dual
union
select '中国','广东','深圳' from dual
union
select '中国','广东','潮州' from dual
union
select '中国','香港',null from dual
union
select '中国','香港','中环' from dual
union
select '中国','台湾',null from dual
commit;
select distinct l1 ,null l2,null l3 from test1
union
select l1, l2,case when max(l3) is not null then null else null end l3 from test1
group by l1,l2
union
select l1,l2,l3 from test1 where l2 is not null and l3 is not null