image.png

我們有這樣一組數(shù)據(jù)，一條數(shù)據(jù)對應(yīng)多個其他表的ID ，儲存格式是以逗號分隔。

我們要將其放在in () 中作為條件，先要把他變成數(shù)組

select * from 表名 where id in (
      SELECT REGEXP_SUBSTR(列名,'[^（分割字符串的符號（比如逗號））]+', 1, LEVEL) FROM DUAL
      CONNECT BY REGEXP_SUBSTR(列名,'[^（分割字符串的符號（比如逗號））]+', 1, LEVEL) IS NOT NULL
)

例子

SELECT
    REGEXP_SUBSTR( ( SELECT t2.DEFINITION_ID FROM BPM_VISUALIZATION_PJ_DEFINITION t2 WHERE t2.ID = 100 ), '[^,]+', 1, LEVEL ) 
FROM
    DUAL CONNECT BY REGEXP_SUBSTR( ( SELECT t2.DEFINITION_ID FROM BPM_VISUALIZATION_PJ_DEFINITION t2 WHERE t2.ID = 100 ), '[^,]+', 1, LEVEL ) IS NOT NULL 

image.png

然后把他作為查詢條件放到括號中

image.png