重复了吧。。 WITH tab1 AS( SELECT 'A' C1, '101,103' C2 FROM DUAL UNION ALL SELECT 'B', '102,103,104' FROM DUAL UNION ALL SELECT 'C', '101,102,103' FROM DUAL UNION ALL SELECT 'D', '101,103,104' FROM DUAL), tab2 AS( SELECT 'ACD' str FROM dual UNION ALL SELECT 'BC' FROM dual UNION ALL SELECT 'BD' FROM dual) SELECT STR, WMSYS.WM_CONCAT(C2) FROM (SELECT STR, C2 FROM (SELECT DISTINCT C1, REGEXP_SUBSTR(C2, '[^,]+', 1, LEVEL) C2 FROM TAB1 CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(C2, '[^,]+')) + 1) TAB1, TAB2 WHERE TAB2.STR LIKE '%' || TAB1.C1 || '%' GROUP BY STR, C2 HAVING COUNT(1) = LENGTH(STR) ORDER BY STR, C2) GROUP BY STR
WITH tab1 AS(
SELECT 'A' C1, '101,103' C2 FROM DUAL UNION ALL
SELECT 'B', '102,103,104' FROM DUAL UNION ALL
SELECT 'C', '101,102,103' FROM DUAL UNION ALL
SELECT 'D', '101,103,104' FROM DUAL),
tab2 AS(
SELECT 'ACD' str FROM dual UNION ALL
SELECT 'BC' FROM dual UNION ALL
SELECT 'BD' FROM dual)
SELECT STR, WMSYS.WM_CONCAT(C2)
FROM (SELECT STR, C2
FROM (SELECT DISTINCT C1, REGEXP_SUBSTR(C2, '[^,]+', 1, LEVEL) C2
FROM TAB1
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(C2, '[^,]+')) + 1) TAB1,
TAB2
WHERE TAB2.STR LIKE '%' || TAB1.C1 || '%'
GROUP BY STR, C2
HAVING COUNT(1) = LENGTH(STR)
ORDER BY STR, C2)
GROUP BY STR