有2个表 Table1 ,Table2 .
Table1的数据为
ID COL
1 A
1 B
2 B
2 A
2 C
3 A
3 C
3 B
4 A
4 C
Table2的数据为
ID COL
1 B
1 A
2 B
2 A
3 C
3 B
3 A
4 A
4 B
然后 将id相同的 但col不同的 选出来
那么结果是
ID TABLE1.COL TABLE2.COL
2 A,B,C A,B //col条数不同
4 A,C A,B //col值不同那么这样SQL这样写 谢谢!
Table1的数据为
ID COL
1 A
1 B
2 B
2 A
2 C
3 A
3 C
3 B
4 A
4 C
Table2的数据为
ID COL
1 B
1 A
2 B
2 A
3 C
3 B
3 A
4 A
4 B
然后 将id相同的 但col不同的 选出来
那么结果是
ID TABLE1.COL TABLE2.COL
2 A,B,C A,B //col条数不同
4 A,C A,B //col值不同那么这样SQL这样写 谢谢!
create table t1 (id number(5),col varchar2(5));
insert into t1 values (1,'A');
insert into t1 values (1,'B');
insert into t1 values (2,'B');
insert into t1 values (2,'A');
insert into t1 values (2,'C');
insert into t1 values (3,'A');
insert into t1 values (3,'C');
insert into t1 values (3,'B');
insert into t1 values (4,'A');
insert into t1 values (4,'C');create table t2 (id number(5),col varchar2(5));
insert into t2 values (1,'B');
insert into t2 values (1,'A');
insert into t2 values (2,'B');
insert into t2 values (2,'A');
insert into t2 values (3,'C');
insert into t2 values (3,'B');
insert into t2 values (3,'A');
insert into t2 values (4,'A');
insert into t2 values (4,'B');
commit;select a.id,a.c1,b.c2
from
(select id,wm_concat(col) c1
from
(
select id,col
from t1
order by id,col
)
group by id) a,
(select id,wm_concat(col) c2
from
(
select id,col
from t2
order by id,col
)
group by id) b
where a.id=b.id and c1 <> c2 id c1 c2
--------------------------------
1 2 A,C,B A,B
2 4 A,C A,B
你好 ! 之前我试过这样的方法了 但是有问题 主要是(select id,wm_concat(col) c1
from
(
select id,col
from t1
order by id,col
)
group by id) a 排序后用wm_concat值不一定是按A,B,C 如果你能按A,B,C的顺序 那么可能是这些值特殊。因为我数据里面的具体值不是这样的都是些字符串
select a.id, a.c1, b.c2
from (select id, max(c1) c1
from (select id,
wm_concat(col) over(partition by id order by col) c1
from (select id, col from t1 order by id, col))
group by id) a,
(select id, max(c2) c2
from (select id,
wm_concat(col) over(partition by id order by col) c2
from (select id, col from t2 order by id, col))
group by id) b
where a.id = b.id
and c1 <> c2;