今天试了下 可以是可以 就是看起来麻烦了:WITH T AS (SELECT 1 PID, 'a,b' FMODEL, 'b,a' SMODEL FROM DUAL UNION ALL SELECT 2 PID, 'a,b,c' FMODEL, 'c,a,b' SMODEL FROM DUAL UNION ALL SELECT 3 PID, 'a,c,d' FMODEL, 'c,a,b' SMODEL FROM DUAL) SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM (SELECT PID, REGEXP_SUBSTR(FMODEL, '[^,]+', 1, LEVEL) FMODEL FROM T CONNECT BY LEVEL <= LENGTH(FMODEL) - LENGTH(REPLACE(FMODEL, ',', '')) + 1 AND PID = PRIOR PID AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL MINUS SELECT PID, REGEXP_SUBSTR(SMODEL, '[^,]+', 1, LEVEL) SMODEL FROM T CONNECT BY LEVEL <= LENGTH(SMODEL) - LENGTH(REPLACE(SMODEL, ',', '')) + 1 AND PID = PRIOR PID AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL) T1 WHERE T1.PID = T.PID) 11g中可以把LENGTH(FMODEL) - LENGTH(REPLACE(FMODEL, ',', '')) + 1换成REGEXP_COUNT(FMODEL, ',') + 1
WITH T AS (SELECT 1 PID, 'a,b' FMODEL, 'b,a' SMODEL FROM DUAL UNION ALL SELECT 2 PID, 'a,b,c' FMODEL, 'c,a,b' SMODEL FROM DUAL UNION ALL SELECT 3 PID, 'a,c,d' FMODEL, 'c,a,b' SMODEL FROM DUAL) SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM (SELECT PID, REGEXP_SUBSTR(FMODEL, '[^,]+', 1, LEVEL) FMODEL FROM T CONNECT BY LEVEL <= LENGTH(FMODEL) - LENGTH(REPLACE(FMODEL, ',', '')) + 1 AND PID = PRIOR PID AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL MINUS SELECT PID, REGEXP_SUBSTR(SMODEL, '[^,]+', 1, LEVEL) SMODEL FROM T CONNECT BY LEVEL <= LENGTH(SMODEL) - LENGTH(REPLACE(SMODEL, ',', '')) + 1 AND PID = PRIOR PID AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL) T1 WHERE T1.PID = T.PID) AND LENGTH(SMODEL)=LENGTH(FMODEL) 这样OK
(SELECT 1 PID, 'a,b' FMODEL, 'b,a' SMODEL FROM DUAL
UNION ALL
SELECT 2 PID, 'a,b,c' FMODEL, 'c,a,b' SMODEL FROM DUAL
UNION ALL
SELECT 3 PID, 'a,c,d' FMODEL, 'c,a,b' SMODEL FROM DUAL)
SELECT *
FROM T
WHERE NOT EXISTS
(SELECT 1
FROM (SELECT PID, REGEXP_SUBSTR(FMODEL, '[^,]+', 1, LEVEL) FMODEL
FROM T
CONNECT BY LEVEL <=
LENGTH(FMODEL) - LENGTH(REPLACE(FMODEL, ',', '')) + 1
AND PID = PRIOR PID
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
MINUS
SELECT PID, REGEXP_SUBSTR(SMODEL, '[^,]+', 1, LEVEL) SMODEL
FROM T
CONNECT BY LEVEL <=
LENGTH(SMODEL) - LENGTH(REPLACE(SMODEL, ',', '')) + 1
AND PID = PRIOR PID
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL) T1
WHERE T1.PID = T.PID)
11g中可以把LENGTH(FMODEL) - LENGTH(REPLACE(FMODEL, ',', '')) + 1换成REGEXP_COUNT(FMODEL, ',') + 1
(SELECT 1 PID, 'a,b' FMODEL, 'b,a' SMODEL FROM DUAL
UNION ALL
SELECT 2 PID, 'a,b,c' FMODEL, 'c,a,b' SMODEL FROM DUAL
UNION ALL
SELECT 3 PID, 'a,c,d' FMODEL, 'c,a,b' SMODEL FROM DUAL)
SELECT *
FROM T
WHERE NOT EXISTS
(SELECT 1
FROM (SELECT PID, REGEXP_SUBSTR(FMODEL, '[^,]+', 1, LEVEL) FMODEL
FROM T
CONNECT BY LEVEL <=
LENGTH(FMODEL) - LENGTH(REPLACE(FMODEL, ',', '')) + 1
AND PID = PRIOR PID
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
MINUS
SELECT PID, REGEXP_SUBSTR(SMODEL, '[^,]+', 1, LEVEL) SMODEL
FROM T
CONNECT BY LEVEL <=
LENGTH(SMODEL) - LENGTH(REPLACE(SMODEL, ',', '')) + 1
AND PID = PRIOR PID
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL) T1
WHERE T1.PID = T.PID) AND LENGTH(SMODEL)=LENGTH(FMODEL)
这样OK