表mytest1,表结构,初始数据如下:
SQL> desc mytest1;
Name Type Nullable Default Comments
----------- -------------- -------- ------- --------
DEPART_ID VARCHAR2(10) Y
OBJECT_NAME VARCHAR2(4000) Y复制代码
SQL> select * from mytest1;
DEPART_IDOBJECT_NAME
---------- --------------------------------------------------------------------------------
helpdesk FILE$,I_FILE1,I_IND1,I_PROXY_ROLE_DATA$_1
human CON$,ICOL$,I_OBJ3
manager C_COBJ#,I_CDEF2,I_OBJ#
spy I_CDEF4,I_FILE#_BLOCK#,I_OBJ5,I_TS#,UET$
tech I_CON1,I_USER1,PROXY_ROLE_DATA$,UNDO$
由于列object_name全部为逗号分隔,希望转化为:
DEPART_IDSUBSTR(OBJECT_NAME,DECODE(LEVE LEVEL
---------- -------------------------------------------------------------------------------- ----------
helpdesk FILE$ 1
helpdesk I_FILE1 2
helpdesk I_IND1 3
helpdesk I_PROXY_ROLE_DATA$_1 4
human CON$ 1
human ICOL$ 2
human I_OBJ3 3
manager C_COBJ# 1
manager I_CDEF2 2
manager I_OBJ# 3
spy I_CDEF4 1
spy I_FILE#_BLOCK#2
spy I_OBJ5 3
spy I_TS# 4
spy UET$ 5
tech I_CON1 1
tech I_USER1 2
tech PROXY_ROLE_DATA$ 3
tech UNDO$ 4
目前我采用的SQL如下:
SELECT distinct depart_id,
substr(object_name,
DECODE(LEVEL,
1,
1,
regexp_count(object_name, ',') + 1,
INSTR(object_name,
',',
1,
regexp_count(object_name, ',')) + 1,
INSTR(object_name, ',', 1, LEVEL - 1) + 1),
DECODE(LEVEL,
regexp_count(object_name, ',') + 1,
INSTR(REVERSE(object_name), ',', 1) - 1,
INSTR(object_name, ',', 1, LEVEL) -
DECODE(LEVEL,
1,
1,
INSTR(object_name, ',', 1, LEVEL - 1) + 1))),
LEVEL
from mytest1
CONNECT BY LEVEL <= regexp_count(object_name, ',') + 1
order by depart_id, level;
由于CONNECT BY 这个地方没有什么限制,所以垃圾数据太多,通过distinct过滤之后数据是没问题,应该还有更简单的SQL。 谁能try 一下。