表名table字段如下
int_id name region_id
1 ni 12,13,14
2 wo 13,14
3 ta 11
处理之后需要查询为：
int_id name region_id
1 ni 12
1 ni 13
1 ni 14
2 wo 13
2 wo 14
3 ta 11
提供sql或者function都可以，谢谢了
WITH TEMP AS
(
SELECT 1 INT_ID,'ni' NAME,'12,13,14' REGION_ID FROM DUAL
UNION
SELECT 2 INT_ID,'wo' NAME,'13,14' REGION_ID FROM DUAL
UNION
SELECT 3 INT_ID,'ta' NAME,'11' REGION_ID FROM DUAL
)
SELECT T1.INT_ID,T1.NAME,REGEXP_SUBSTR(T1.REGION_ID,'[0-9]+',1,T2.LEV) REGION_ID FROM
(SELECT TEMP.*,LENGTH(REGION_ID)-LENGTH(REPLACE(REGION_ID,',',''))+1 RN FROM TEMP) T1,
(SELECT LEVEL LEV FROM DUAL
CONNECT BY LEVEL <= (SELECT SUM(LENGTH(REGION_ID)-LENGTH(REPLACE(REGION_ID,',',''))+1) RN FROM TEMP)) T2
WHERE T2.LEV <= T1.RN ORDER BY INT_ID,LEV
INT_ID NAME REGION_ID
1 ni 12
1 ni 13
1 ni 14
2 wo 13
2 wo 14
3 ta 11
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/21220558/viewspace-590890/ ，如需转载，请注明出处，否则将追究法律责任。
