TO >> zjcxc(邹建) 可以不显示其它字段,但过程中必须比较两个表中其它所有字段的值,我要的结果集是kf.dbo.info表中除去和wch.dbo.info表相同记录后taxi_no字段的Group by 值
--找出 kf库info表中 除去 <与wch库的info表相同记录> 以外的记录 --只要有任何一个字段值不一样都视为不同. --最后还要 Group by taxi_no(未放入group by 的字段必须放入聚合函数,所以假设其他字段都放入max中)use kf go--处理示例 declare @s nvarchar(4000),@fd nvarchar(4000) select @s=N'',@fd=N'' select @s=@s+N' and '+name+N'=a.'+name ,@fd=@fd+case when name=N'[taxi_no]' then @fd else @fd+N','+name+N'=max('+name+')' end from( select name=quotename(name) from syscolumns where id=object_id(N'[info]'))a select @s=stuff(@s,1,4,'') exec(N'select [taxi_no]'+@fd+N' from [info] a where not exists( select * from wch..[info] where'+@s+N') group by [taxi_no]')
right join 存储的问题是记录重复时,可能产生多对多的情况
--如果不管其他字段use kf go--处理示例 declare @s nvarchar(4000) select @s=N'' select @s=@s+N' and '+name+N'=a.'+name from( select name=quotename(name) from syscolumns where id=object_id(N'[info]'))a select @s=stuff(@s,1,4,'') exec(N'select taxi_no from [info] a where not exists( select * from wch..[info] where'+@s+N') group by taxi_no')
union
select distinct col1,col2,col3,... from wch.dbo.info用union而不是 union all
>>zjcxc(邹建) 可以不显示其它字段,但在判断时必须判断两个表中其它所有字段的值
--只要有任何一个字段值不一样都视为不同.
--最后还要 Group by taxi_no(未放入group by 的字段必须放入聚合函数,所以假设其他字段都放入max中)use kf
go--处理示例
declare @s nvarchar(4000),@fd nvarchar(4000)
select @s=N'',@fd=N''
select @s=@s+N' and '+name+N'=a.'+name
,@fd=@fd+case
when name=N'[taxi_no]' then @fd
else @fd+N','+name+N'=max('+name+')' end
from(
select name=quotename(name) from syscolumns
where id=object_id(N'[info]'))a
select @s=stuff(@s,1,4,'')
exec(N'select [taxi_no]'+@fd+N'
from [info] a
where not exists(
select * from wch..[info]
where'+@s+N')
group by [taxi_no]')
go--处理示例
declare @s nvarchar(4000)
select @s=N''
select @s=@s+N' and '+name+N'=a.'+name
from(
select name=quotename(name) from syscolumns
where id=object_id(N'[info]'))a
select @s=stuff(@s,1,4,'')
exec(N'select taxi_no
from [info] a
where not exists(
select * from wch..[info]
where'+@s+N')
group by taxi_no')