3.3w+
社区成员
- 全部
- 基础类
- 应用实例
- 新技术前沿
如何比较两张内容差不多的表？
为了保证输入的准确性，现在有两人往两种同样结构的表中输入同样的数据，假设每个人输入的主键字段都正确，最后应该如何审核两张表的其他字段是否相同？
...全文
当前发帖距今超过3年，不再开放新的回复
发表回复
zjcxc 元老 2003-10-09
如果不想手工写字段,可以用生成SQL语句的方法:
declare @sql varchar(8000)
set @sql='select Select * from A a inner join B b on a.key = b.key where '
select @sql=@sql+'a.['+name+']<>b.['+name+'] or '
from syscolumns where object_id('A')=id and name<>'key'
set @sql=left(@sql,len(@sql)-4)
exec(@sql)
declare @sql varchar(8000)
set @sql='select Select * from A a inner join B b on a.key = b.key where '
select @sql=@sql+'a.['+name+']<>b.['+name+'] or '
from syscolumns where object_id('A')=id and name<>'key'
set @sql=left(@sql,len(@sql)-4)
exec(@sql)
zjcxc 元老 2003-10-09
逐个比较,
Select * from A a inner join B b on a.key = b.key
where a.F1 <> b.F1 or a.F2 <> b.F2 or a.F3 <> b.F3 ...其他字段逐个比较
Select * from A a inner join B b on a.key = b.key
where a.F1 <> b.F1 or a.F2 <> b.F2 or a.F3 <> b.F3 ...其他字段逐个比较
ywduan 2003-10-09
非常感谢
都不错 我试试成功后结贴
都不错 我试试成功后结贴
txlicenhe 2003-10-09
Select * from A
join B on a.key = b.key
where a.F1 <> b.F1 or a.F2 <> b.F2 or a.F3 <> b.F3 ...
join B on a.key = b.key
where a.F1 <> b.F1 or a.F2 <> b.F2 or a.F3 <> b.F3 ...
pengdali 2003-10-09
差：
c1-c2:
select * from t1 where not exists(select 1 from t2 where t1.c1=t2.c1 and t1.c2=t2.c2)
c2-c1:
select * from t2 where not exists(select 1 from t1 where t1.c1=t2.c1 and t1.c2=t2.c2)
c1-c2:
select * from t1 where not exists(select 1 from t2 where t1.c1=t2.c1 and t1.c2=t2.c2)
c2-c1:
select * from t2 where not exists(select 1 from t1 where t1.c1=t2.c1 and t1.c2=t2.c2)
ywduan 2003-10-09
SQL版的人真热心啊
谢谢
谢谢
