--创建测试环境
create table A
(
ID int,
Name varchar(10)
)
create table B
(
ID int,
Name varchar(10)
)
insert A
select 1,'AAA' union
select 2,'BBB'
insert B
select 1,'AAA' union
select 2,'BBB' union
select 3,'CCC' union
select 4,'DDD'
--测试
declare @sql varchar(1000),@col varchar(10)
select @sql=isnull(@sql,'')+' and A.['+name+']=B.['+name+']',
@col=name
from syscolumns where id=object_id('A')
select @sql='select B.* from B left join A on '+stuff(@sql,1,4,'')+' where A.['+@col+'] is null'
exec(@sql)
--删除测试环境
drop table A,B
--结果
/*
ID Name
----------- ----------
3.00 CCC
4.00 DDD
*/
declare @sql varchar(8000),@col varchar(10)
select @sql=isnull(@sql,'')+' and A.['+name+']=B.['+name+']',
@col=name
from syscolumns where id=object_id('A')
select @sql='select B.* from B left join A on '+stuff(@sql,1,4,'')+' where A.['+@col+'] is null'
exec(@sql)