with t as (select 'AA,BB,CC' a, 'CC,BB,AA' b from dual union all select 'AA1,BB,CC,D' a, 'CC,BB,AA2,D' b from dual union all select 'AA2,BB' a, 'AA2,BB,D' b from dual) select t.a, t.b, decode(listagg(str1) within group(order by str1), listagg(str2) within group(order by str2), 1, 0) flag from (select distinct t.a, t.b, regexp_substr(t.a, '[^,]+', 1, level) str1, regexp_substr(t.b, '[^,]+', 1, level) str2 from t connect by level <= greatest(regexp_count(t.a, ','), regexp_count(t.b, ',')) + 1) t group by t.a, t.b;
http://blog.csdn.net/roy_88/article/details/6930577
(select 'AA,BB,CC' a, 'CC,BB,AA' b
from dual
union all
select 'AA1,BB,CC,D' a, 'CC,BB,AA2,D' b
from dual
union all
select 'AA2,BB' a, 'AA2,BB,D' b from dual)
select t.a,
t.b,
decode(listagg(str1) within group(order by str1),
listagg(str2) within group(order by str2),
1,
0) flag
from (select distinct t.a,
t.b,
regexp_substr(t.a, '[^,]+', 1, level) str1,
regexp_substr(t.b, '[^,]+', 1, level) str2
from t
connect by level <=
greatest(regexp_count(t.a, ','), regexp_count(t.b, ',')) + 1) t
group by t.a, t.b;
答案对了就行 多学习下oracle正则把,有时候挺管用,挺方便