oracle中,已知机构路径记录，如何求所有的机构树记录表。

Question

机构路径如：
中国-广东-广州
中国-广东-深圳
中国-广东-潮州
中国-香港
中国-香港-中环
中国-台湾
机构树状结果：
level1 level2 level3
中国
中国    广东
中国    广东   广州
中国    广东   深圳
中国    广东   潮州
中国    香港
中国    香港   中环
中国    台湾有和sys_connect_by_path相反的函数吗？或者怎么生成相要的？

Answer 1

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

Answer 2

lixinbill，你误会我意思，原始数据是含有“-”的路径，如“中国-广东-广州”

Answer 3

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
上面是3层得写法 如果7层得话就得分 l1,l2,l3,l4,l5,l6,l7了 只会复杂繁琐 不会重复的

Answer 4

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 发个拆分的sql

Answer 5

select distinct l1 ,null l2,null l3,null l4,null l5,null l6,null l7 from test22unionselect L1,L2 ,case when max(L3) is not null then null else null end  L3 from test22
group by L1,L2
union
select l1,l2,l3,l4,l5,l6,l7 from test22 where l2 is not null and l3 is not null
and l4 is not null and l5 is not null
and l6 is not null and l7 is not null提示 错误
query block has incorrect number of result columns

Answer 6

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

Answer 7

还要稍微改一下：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行。

Answer 8

vber1010给出的答案我试运行后，树层次假定7，记录数1300条，好像运行不出结果，不知怎么回事。