请教一个递归sql的写法

[want1615]

我想得到


请教下各位，这个sql该怎么写。

[want1615]

哦，想要的结果格式错了。
是

[CT_LXL]

引用 1 楼 want1615 的回复:

哦，想要的结果格式错了。 是

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;

[youngman123456]

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

主要用到了层次化查询和正则表达式

[want1615]

这样写不行啊。都是写死的。目录可以无限级。

[小灰狼W]

可以的。上面的是测试数据，无视就行 稍微改一下，性能会好一些 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