先写了一个,希望能满足你的需求。 [code=sql]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[/code]
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) select distinct t1.c1, (case when t1.c2 > t2.c2 then t2.c2 else t1.c2 end) c1, (case when t1.c2 > t2.c2 then t1.c2 else t2.c2 end) c2 from (SELECT DISTINCT C1, REGEXP_SUBSTR(C2, '[^,]+', 1, LEVEL) C2 FROM TAB1 CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(C2, '[^,]+')) + 1) t1, (SELECT DISTINCT C1, REGEXP_SUBSTR(C2, '[^,]+', 1, LEVEL) C2 FROM TAB1 CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(C2, '[^,]+')) + 1) t2 where t1.c1 = t2.c1 and t1.c2 <> t2.c2 group by t1.c1, t1.c2, t2.c2 order by t1.c1
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) select replace(wmsys.wm_concat(distinct t1.c1),',','') c1, (case when t1.c2 > t2.c2 then t2.c2 else t1.c2 end) || ',' || (case when t1.c2 > t2.c2 then t1.c2 else t2.c2 end) c2 from (SELECT DISTINCT C1, REGEXP_SUBSTR(C2, '[^,]+', 1, LEVEL) C2 FROM TAB1 CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(C2, '[^,]+')) + 1) t1, (SELECT DISTINCT C1, REGEXP_SUBSTR(C2, '[^,]+', 1, LEVEL) C2 FROM TAB1 CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(C2, '[^,]+')) + 1) t2 where t1.c1 = t2.c1 and t1.c2 <> t2.c2 group by (case when t1.c2 > t2.c2 then t2.c2 else t1.c2 end), (case when t1.c2 > t2.c2 then t1.c2 else t2.c2 end) having count(1)>2 帮你写完得了。。应该和你要的效果是一样的
select wm_concat(distinct c1_1) c1, substr(factor, 2) factor from (select c1_1, c1_2, c2_1, sys_connect_by_path(keyword, ',') factor from (select a.c1 c1_1, a.c2 c2_1, b.c1 c1_2, b.keyword, row_number() over(partition by b.c1, a.c1 order by b.keyword) rn from tab1 a, (select distinct c1, REGEXP_SUBSTR(C2, '[^,]+', 1, LEVEL) keyword from tab1 connect by level <= LENGTH(REGEXP_REPLACE(C2, '[^,]+')) + 1) b where a.c1 != b.c1 and instr(',' || a.c2 || ',', ',' || b.keyword || ',') > 0) where level >= 2 start with rn = 1 connect by prior c1_1 = c1_1 and prior c1_2 = c1_2 and prior rn = rn - 1) group by factor
如果表内容是下面的结构,会不会简单些: C1 C2 A 101 A 103 B 102 B 103 B 104 C 101 C 102 C 103 D 101 D 103 D 104
是,这样的话,我就不用帮你拆分了,相当于剩了一步,效率也会提高很多,直接自关联就可以了 select replace(wmsys.wm_concat(distinct t1.c1),',','') c1, (case when t1.c2 > t2.c2 then t2.c2 else t1.c2 end) || ',' || (case when t1.c2 > t2.c2 then t1.c2 else t2.c2 end) c2 from tab t1,tab t2 where t1.c1 = t2.c1 and t1.c2 <> t2.c2 group by (case when t1.c2 > t2.c2 then t2.c2 else t1.c2 end), (case when t1.c2 > t2.c2 then t1.c2 else t2.c2 end) having count(1)>2
是,这样的话,我就不用帮你拆分了,相当于剩了一步,效率也会提高很多,直接自关联就可以了 select replace(wmsys.wm_concat(distinct t1.c1),',','') c1, (case when t1.c2 > t2.c2 then t2.c2 else t1.c2 end) || ',' || (case when t1.c2 > t2.c2 then t1.c2 else t2.c2 end) c2 from tab t1,tab t2 where t1.c1 = t2.c1 and t1.c2 <> t2.c2 group by (case when t1.c2 > t2.c2 then t2.c2 else t1.c2 end), (case when t1.c2 > t2.c2 then t1.c2 else t2.c2 end) having count(1)>2 思维不错,明日之星啊
是,这样的话,我就不用帮你拆分了,相当于剩了一步,效率也会提高很多,直接自关联就可以了 select replace(wmsys.wm_concat(distinct t1.c1),',','') c1, (case when t1.c2 > t2.c2 then t2.c2 else t1.c2 end) || ',' || (case when t1.c2 > t2.c2 then t1.c2 else t2.c2 end) c2 from tab t1,tab t2 where t1.c1 = t2.c1 and t1.c2 <> t2.c2 group by (case when t1.c2 > t2.c2 then t2.c2 else t1.c2 end), (case when t1.c2 > t2.c2 then t1.c2 else t2.c2 end) having count(1)>2 思维不错,明日之星啊 大大啊,趁有空瞎琢磨的。。
先写了一个,希望能满足你的需求。
[code=sql]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[/code]
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
上面的不知道是那么原因,有点乱,看这个吧。。
你要将TAB1、TAB2替换为你自己的表
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)
select distinct t1.c1,
(case
when t1.c2 > t2.c2 then
t2.c2
else
t1.c2
end) c1,
(case
when t1.c2 > t2.c2 then
t1.c2
else
t2.c2
end) c2
from (SELECT DISTINCT C1, REGEXP_SUBSTR(C2, '[^,]+', 1, LEVEL) C2
FROM TAB1
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(C2, '[^,]+')) + 1) t1,
(SELECT DISTINCT C1, REGEXP_SUBSTR(C2, '[^,]+', 1, LEVEL) C2
FROM TAB1
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(C2, '[^,]+')) + 1) t2
where t1.c1 = t2.c1
and t1.c2 <> t2.c2
group by t1.c1, t1.c2, t2.c2
order by t1.c1
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)
select replace(wmsys.wm_concat(distinct t1.c1),',','') c1,
(case when t1.c2 > t2.c2 then t2.c2 else t1.c2 end) || ',' ||
(case when t1.c2 > t2.c2 then t1.c2 else t2.c2 end) c2
from (SELECT DISTINCT C1, REGEXP_SUBSTR(C2, '[^,]+', 1, LEVEL) C2
FROM TAB1
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(C2, '[^,]+')) + 1) t1,
(SELECT DISTINCT C1, REGEXP_SUBSTR(C2, '[^,]+', 1, LEVEL) C2
FROM TAB1
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(C2, '[^,]+')) + 1) t2
where t1.c1 = t2.c1
and t1.c2 <> t2.c2
group by (case when t1.c2 > t2.c2 then t2.c2 else t1.c2 end),
(case when t1.c2 > t2.c2 then t1.c2 else t2.c2 end)
having count(1)>2
帮你写完得了。。应该和你要的效果是一样的
from (select c1_1, c1_2, c2_1, sys_connect_by_path(keyword, ',') factor
from (select a.c1 c1_1,
a.c2 c2_1,
b.c1 c1_2,
b.keyword,
row_number() over(partition by b.c1, a.c1 order by b.keyword) rn
from tab1 a,
(select distinct c1,
REGEXP_SUBSTR(C2, '[^,]+', 1, LEVEL) keyword
from tab1
connect by level <=
LENGTH(REGEXP_REPLACE(C2, '[^,]+')) + 1) b
where a.c1 != b.c1
and instr(',' || a.c2 || ',', ',' || b.keyword || ',') > 0)
where level >= 2
start with rn = 1
connect by prior c1_1 = c1_1
and prior c1_2 = c1_2
and prior rn = rn - 1)
group by factor
C1 C2
A 101
A 103
B 102
B 103
B 104
C 101
C 102
C 103
D 101
D 103
D 104
select replace(wmsys.wm_concat(distinct t1.c1),',','') c1,
(case when t1.c2 > t2.c2 then t2.c2 else t1.c2 end) || ',' ||
(case when t1.c2 > t2.c2 then t1.c2 else t2.c2 end) c2
from tab t1,tab t2
where t1.c1 = t2.c1
and t1.c2 <> t2.c2
group by (case when t1.c2 > t2.c2 then t2.c2 else t1.c2 end),
(case when t1.c2 > t2.c2 then t1.c2 else t2.c2 end)
having count(1)>2
select replace(wmsys.wm_concat(distinct t1.c1),',','') c1,
(case when t1.c2 > t2.c2 then t2.c2 else t1.c2 end) || ',' ||
(case when t1.c2 > t2.c2 then t1.c2 else t2.c2 end) c2
from tab t1,tab t2
where t1.c1 = t2.c1
and t1.c2 <> t2.c2
group by (case when t1.c2 > t2.c2 then t2.c2 else t1.c2 end),
(case when t1.c2 > t2.c2 then t1.c2 else t2.c2 end)
having count(1)>2
思维不错,明日之星啊
select replace(wmsys.wm_concat(distinct t1.c1),',','') c1,
(case when t1.c2 > t2.c2 then t2.c2 else t1.c2 end) || ',' ||
(case when t1.c2 > t2.c2 then t1.c2 else t2.c2 end) c2
from tab t1,tab t2
where t1.c1 = t2.c1
and t1.c2 <> t2.c2
group by (case when t1.c2 > t2.c2 then t2.c2 else t1.c2 end),
(case when t1.c2 > t2.c2 then t1.c2 else t2.c2 end)
having count(1)>2
思维不错,明日之星啊
大大啊,趁有空瞎琢磨的。。