oracle查询多个id,oracle 一个字段存多个ID(以逗号","分割),连表查询并也放入一个字段中...

欢迎转载但请注明出处：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,