17,082
社区成员
发帖
与我相关
我的任务
分享
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
[/quote]
思维不错,明日之星啊[/quote]
大大啊,趁有空瞎琢磨的。。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
[/quote]
思维不错,明日之星啊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 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
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
帮你写完得了。。应该和你要的效果是一样的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