1、一行
SELECT T.ID, REGEXP_SUBSTR(T.VALS, '[^,]+', 1, LEVEL) AS VAL FROM (SELECT '101' ID, 'A,B' VALS FROM DUAL) T CONNECT BY LEVEL <= REGEXP_COUNT(T.VALS, '[^,]+');
2、多行
2-1、如果ID唯一不重复:
SELECT T.ID, REGEXP_SUBSTR(T.VALS, '[^,]+', 1, LEVEL) AS VAL FROM ( SELECT '101' ID, 'A,B' VALS FROM DUAL UNION ALL SELECT '102' ID, 'X,X' VALS FROM DUAL ) T CONNECT BY LEVEL <= REGEXP_COUNT(T.VALS, '[^,]+') AND PRIOR T.ID = T.ID AND PRIOR DBMS_RANDOM.VALUE() IS NOT NULL;
2-2、如果ID有重复:
SELECT T.RM, T.ID, REGEXP_SUBSTR(T.VALS, '[^,]+', 1, LEVEL) AS VAL FROM (
SELECT ROWNUM RM, A.* FROM ( SELECT '101' ID, 'A,B' VALS FROM DUAL UNION ALL SELECT '101' ID, 'C,D' VALS FROM DUAL UNION ALL SELECT '102' ID, 'X,X' VALS FROM DUAL ) A ) T CONNECT BY LEVEL <= REGEXP_COUNT(T.VALS, '[^,]+') AND PRIOR T.RM = T.RM AND PRIOR DBMS_RANDOM.VALUE() IS NOT NULL;
标签:DUAL,LEVEL,VALS,CONNECT,拆分,Oracle,ID,SELECT From: https://www.cnblogs.com/lgx5/p/17517764.html