解决方案 »
- 关于SQLP LUS客户端连接问题
- 新手提问:在ORACLE里怎么执行带多个输出参数的存储过程
- 请教大家 序列号问题
- Oracle可以建带参数的视吗?
- Oracle Directory Manager 连接问题
- oracle的sqlload函数加载数据后,结果成功了,但生成的日志文件中却又错误,请帮忙看看,谢谢。
- orcale 中的数据类型中 varchar2 和varchar 有什么区别???
- oralce查询sql问题,急!
- 字符串很急的问题!请大家帮忙!!
- 哪有oracle8.05的简体中文企业版的下载呀!!
- OracleBulkCopy数据重复导致索引失效
- 高分求解 用VBA导出oracle数据库的clob数据
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主要用到了层次化查询和正则表达式