有两个表,A表中
ID TEXT
1 test
2 tes
3 asdf
9 kkk
B表中
ID TEXT
1 112324
2 sdffghj
3 test
4 tes
5 asdf
怎么对比,把B表和A表中不同的数据取出来.
这样写可以吗:select a.text,b.text from a,b group by ......
还是:select text from a.b where a.text<>b.text。
不知道是不是都是错的,盼望高手教教,上面两句有什么问题。谢谢
ID TEXT
1 test
2 tes
3 asdf
9 kkk
B表中
ID TEXT
1 112324
2 sdffghj
3 test
4 tes
5 asdf
怎么对比,把B表和A表中不同的数据取出来.
这样写可以吗:select a.text,b.text from a,b group by ......
还是:select text from a.b where a.text<>b.text。
不知道是不是都是错的,盼望高手教教,上面两句有什么问题。谢谢
select a.*,b.*
from a full join b on a.text=b.text
where a.id is null or b.id is null
union all
select * from b except select * from a
(select * from a inersect select * from b)
(select * from a union select * from b) except
(select * from a intersect select * from b)
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-07-31 21:40:58
----------------------------------------------------------------
--> 测试数据:[ta]
if object_id('[ta]') is not null drop table [ta]
create table [ta]([ID] int,[TEXT] varchar(4))
insert [ta]
select 1,'test' union all
select 2,'tes' union all
select 3,'asdf' union all
select 9,'kkk'
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([ID] int,[TEXT] varchar(7))
insert [tb]
select 1,'112324' union all
select 2,'sdffghj' union all
select 3,'test' union all
select 4,'tes' union all
select 5,'asdf'
--------------开始查询--------------------------
select * from tb where id not in (select ta.id from ta,tb where ta.[text]!=tb.[text])
----------------结果----------------------------
/*ID TEXT
----------- -------
4 tes
5 asdf(所影响的行数为 2 行)
*/