create table A(银行卡号 varchar(5),金额 int,日期 datetime,交易号 varchar(100))insert into Aselect 'xxxx1','100',getdate(),'001' union allselect 'xxxx2','200',getdate(),'002' union allselect 'xxxx3','300',getdate(),'003' union allselect 'xxxx4','400',getdate(),'004'create table B(银行卡号 varchar(5),金额 int,日期 datetime,交易号 varchar(100))insert into Bselect 'xxxx1','100',getdate(),'001' union allselect 'xxxx5','200',getdate(),'002' union allselect 'xxxx3','400',getdate(),'003' union allselect 'xxxx4','400',getdate(),'005'select (case when A.交易号 is null then 'B表多' when B.交易号 is null then 'A表多' when A.银行卡号!=B.银行卡号 then '卡号不一致' when A.金额!=B.金额 then '金额不正确' else '正确' end) as 比较结果, isnull(a.交易号,b.交易号) as 交易号, isnull(a.日期 ,b.日期 ) as 日期 , a.银行卡号, a.金额 , b.银行卡号, b.金额 from Afull outer join Bon A.交易号=b.交易号===============================正确 001 2009-07-16 17:02:06.390 xxxx1 100 xxxx1 100卡号不一致 002 2009-07-16 17:02:06.390 xxxx2 200 xxxx5 200金额不正确 003 2009-07-16 17:02:06.390 xxxx3 300 xxxx3 400A表多 004 2009-07-16 17:02:06.390 xxxx4 400 NULL NULLB表多 005 2009-07-16 17:02:03.950 NULL NULL xxxx4 400------测试成功！
