with t as (select 0 id_dir, null id_dir_p, '/' dir_nm from dual union all select 1 id_dir, 0 id_dir_p, 'test' dir_nm from dual union all select 2 id_dir, 0 id_dir_p, 'test2' dir_nm from dual union all select 3 id_dir, 2 id_dir_p, 'test2_01' dir_nm from dual union all select 4 id_dir, 2 id_dir_p, 'test2_02' dir_nm from dual) select id_dir, regexp_replace(ltrim(min(SYS_CONNECT_BY_PATH(dir_nm, ',')), ','), '[/]?,', '/') from t connect by prior id_dir = id_dir_p group by id_dir order by id_dir;
可以的。上面的是测试数据,无视就行稍微改一下,性能会好一些 select id_directory, regexp_replace(ltrim(SYS_CONNECT_BY_PATH(directory_name, ','),','),'/?,','/') from t start with id_directory_parent is null connect by prior id_directory = id_directory_parent
WITH a AS( SELECT 0 id_dir1,NULL id_dir2,'' dir_name FROM dual UNION ALL SELECT 1,0,'TEST' FROM dual UNION ALL SELECT 2,0,'TEST2' FROM dual UNION ALL SELECT 3,2,'TEST2_01' FROM dual UNION ALL SELECT 4,2,'TEST2_02' FROM dual ) SELECT id_dir1 id_directory,regexp_replace(sys_connect_by_path(dir_name,'/'),'^/{2}','/')directory_name FROM a START WITH id_dir1=0 CONNECT BY PRIOR id_dir1=id_dir2 ORDER BY id_directory主要用到了层次化查询和正则表达式
with t as
(select 0 id_dir, null id_dir_p, '/' dir_nm
from dual
union all
select 1 id_dir, 0 id_dir_p, 'test' dir_nm
from dual
union all
select 2 id_dir, 0 id_dir_p, 'test2' dir_nm
from dual
union all
select 3 id_dir, 2 id_dir_p, 'test2_01' dir_nm
from dual
union all
select 4 id_dir, 2 id_dir_p, 'test2_02' dir_nm from dual)
select id_dir,
regexp_replace(ltrim(min(SYS_CONNECT_BY_PATH(dir_nm, ',')), ','),
'[/]?,',
'/')
from t
connect by prior id_dir = id_dir_p
group by id_dir
order by id_dir;
select id_directory,
regexp_replace(ltrim(SYS_CONNECT_BY_PATH(directory_name, ','),','),'/?,','/')
from t
start with id_directory_parent is null
connect by prior id_directory = id_directory_parent
SELECT 0 id_dir1,NULL id_dir2,'' dir_name FROM dual
UNION ALL
SELECT 1,0,'TEST' FROM dual
UNION ALL
SELECT 2,0,'TEST2' FROM dual
UNION ALL
SELECT 3,2,'TEST2_01' FROM dual
UNION ALL
SELECT 4,2,'TEST2_02' FROM dual
)
SELECT id_dir1 id_directory,regexp_replace(sys_connect_by_path(dir_name,'/'),'^/{2}','/')directory_name FROM a
START WITH id_dir1=0 CONNECT BY PRIOR id_dir1=id_dir2 ORDER BY id_directory主要用到了层次化查询和正则表达式