欢迎转载但请注明出处:https://blog.csdn.net/qq_42440234/article/details/84101412
TABLE_A:
TABLE_B:
TABLE_A中的的WATER_RANGE存的是TABLE_B的ID,以逗号区分
下图为查询后的结果:
SQL如下:
WITH TABLE_A_ AS
(
SELECT ID,WATER_RANGE ,LEVEL C_LEVEL,REGEXP_SUBSTR(WATER_RANGE , '[^,]+', 1, LEVEL)
CANAL_ID FROM TABLE_A
CONNECT BY LEVEL <= REGEXP_COUNT(WATER_RANGE , '[^,]+')
AND PRIOR ID = ID
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
)
SELECT A.ID , A.WATER_RANGE, LISTAGG(TO_CHAR(B.CANAL_NAME),',') WITHIN
GROUP(ORDER BY C_LEVEL) CANAL_NAME FROM TABLE_A_ A
INNER JOIN TALBE_B B ON A.CANAL_ID = B."ID" GROUP BY A.ID, A.WATER_RANGE ;
注:LISTAGG里的TO_CHAR视情况而定,我查询出来乱码才加的TO_CHAR,