Question

sql问题:在两个相同的表中找出不同的记录.

有两个表:(结构相同,但记录不完全相同)
表:a
col_a col_b
1 a
2 a
3 b

表:b
col_a col_b
1 a
2 a
3 a
4 b

找出表a和表b中不同的记录,就是要找出表b中的3,4两条记录.
写出sql语句.

Answer 1

select * from a where not exists(select * from b where a.col_a=b.col_a and a.col_b=b.col_b) union select * from b where not exists(select * from a where a.col_a=b.col_a and a.col_b=b.col_b)

以上得出两表中所有不同记录（a表中第三条，b表中3、4条）

Answer 2

如果b表的记录在a表中不存在,就取出该记录
select * from tb where col_a not in(select ta.col_a from ta,tb where ta.col_a=tb.col_a and ta.col_b=tb.col_b)

也可以用exists语句
select * from tb c where not exists(select * from ta,tb where ta.ca=tb.ca and ta.cb=tb.cb and c.ca=ta.ca)

Answer 3

(select a.* from a,b where a.col_a<>b.col_a or a.col_b<>b.col_b) union (select b.* from a,b where a.col_a<>b.col_a or a.col_b<>b.col_b)

Answer 4

我想了半天,想不出用sql语句该如何处理

我的意思是分别读出数据 到数组A,B中再做相应处理

--------
请教楼下的高见..