oracle 同一张表比较两不同数值字段?
本帖最后由 a276202031 于 2014-03-04 14:04:59 编辑
例如:
表Track中有
Model 和 DealModel 两个字段
表内容为:
Pid Model DealModel
01 a,b b,a
02 a,b,c c,b,a
在查询的条件中要体现出 Model=DealModel
Model 和 DealModel 俩个字段中的内容是被打乱的 但查询结果要显示两字段内容是相等的
------解决方案--------------------
今天试了下 可以是可以 就是看起来麻烦了: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