select * from t1 where convert(varchar(10),列1)+'-'+convert(varchar(10),列2)+'-'+convert(varchar(10),列3) not in (select convert(varchar(10),列1)+'-'+convert(varchar(10),列2)+'-'+convert(varchar(10),列3) from t2)
调试欢乐多
declare @a table (列1 int,列2 nvarchar(10),列3 nvarchar(10))
declare @b table (列1 int,列2 nvarchar(10),列3 nvarchar(10))
insert into @a select 1,'a1','b1'
union all select 1,'a2','b2'
union all select 2,'a6','b6'
insert into @b select 1,'a1','b2'
union all select 1,'a2','b2'
union all select 3,'a6','b6'
select * from @a a where not exists (select 1 from @b b where a.列1=b.列1 and a.列2=b.列2 and a.列3=b.列3)列1 列2 列3
----------- ---------- ----------
1 a1 b1
2 a6 b6(2 行受影响)
go
create table [表1]([列1] int,[列2] varchar(2),[列3] varchar(2))
insert [表1]
select 1,'a1','b1' union all
select 1,'a2','b2' union all
select 2,'a6','b6'
if object_id('[表2]') is not null drop table [表2]
go
create table [表2]([列1] int,[列2] varchar(2),[列3] varchar(2))
insert [表2]
select 1,'a1','b2' union all
select 1,'a2','b2' union all
select 3,'a6','b6'--select * from [表1]
--select * from [表2]select * from [表1]
where checksum(*) not in (select checksum(*) from [表2])
--测试结果:
/*
列1 列2 列3
----------- ---- ----
1 a1 b1
2 a6 b6(2 行受影响)
*/
create table tb2(col1 int,col2 nvarchar(10),col3 nvarchar(10))
insert into tb1 select 1,'a1','b1'
union all select 1,'a2','b2'
union all select 2,'a6','b6'
insert into tb2 select 1,'a1','b2'
union all select 1,'a2','b2'
union all select 3,'a6','b6'select t.* from tb1 t where not exists (select 1 from tb2 where col1 = t.col1 and col2 = t.col2 and col3 = t.col3)drop table tb1 , tb2/*
col1 col2 col3
----------- ---------- ----------
1 a1 b1
2 a6 b6(所影响的行数为 2 行)
*/
select * from tb1
except
select * from tb2
EXCEPT
select * from [表2]/**
列1 列2 列3
----------- ---- ----
1 a1 b1
2 a6 b6(所影响的行数为 2 行)
**/
1.not exists 来排除相同的
2.对每条记录取一个值来唯一表示该记录,然后通过比较该值来判断是是否相同并排除。这个又有2种办法即1楼的和4楼的
1楼将每条记录中的所有列值取出后合为一个值进行比较(该方法,本人未测试)
4楼则是利用checksum来取每条记录相应列的哈希值,代码看上去比较neat------------------------
另外,也有朋友提到2005下的except方法,我也很想用这个方法,看上去代码更少更整洁,但是谁让sql 2000 不支持呢。