关于#oracle#逗号分割的多值匹配问题

Question

目前存在两个表A和B，
假设A.ID='1,2,3'
B.ID='3,1,5,2,6,8,4'
怎么处理才能得知A.ID全在B.ID里呢？

Answer 1

基于Monster 组和GPT的调写：

将A.ID和B.ID中的数字转化为行，然后使用INNER JOIN和GROUP BY来找到A.ID中的所有数字都存在于B.ID中的情况。

SELECT A.ID 
FROM 
   (SELECT REGEXP_SUBSTR(ID, '[^,]+', 1, LEVEL) ID FROM A 
    CONNECT BY REGEXP_SUBSTR(ID, '[^,]+', 1, LEVEL) IS NOT NULL) A
INNER JOIN 
   (SELECT REGEXP_SUBSTR(ID, '[^,]+', 1, LEVEL) ID FROM B 
    CONNECT BY REGEXP_SUBSTR(ID, '[^,]+', 1, LEVEL) IS NOT NULL) B
ON A.ID = B.ID
GROUP BY A.ID
HAVING COUNT(*) = (SELECT COUNT(*) FROM (SELECT REGEXP_SUBSTR(ID, '[^,]+', 1, LEVEL) ID FROM A 
CONNECT BY REGEXP_SUBSTR(ID, '[^,]+', 1, LEVEL) IS NOT NULL));