从两张表中取出不一致的数据

DECLARE   @verify   TABLE  (h883id  char ( 9 )  not   null )
DECLARE   @outmemo   TABLE  (h883id  char ( 9 )  not   null )
DECLARE   @t1   TABLE  (h883id  char ( 9 )  not   null )

declare   @strCorpID   char ( 4 )
declare   @StartDate   DateTime
declare   @EndDate   DateTime
declare   @count   int
set   @strCorpID = ' C275 '
-- set @StartDate='1900-04-01'
-- set @EndDate = '2008-02-03'

select   @startdate = startdate, @enddate = enddate  from  verifymain
where  corpid = @strcorpid
-- print @startdate
-- print @enddate

insert   into   @outmemo  
select   distinct  h883id  from  (
-- 成品报关出区：
select  om.h883id, ' 成品 '   as  goodstype, -- ps.oriproductid,
ps.productpartno,ps.hscode,ps.chinesename,ps.spec,ps.type,
sum (oub.quantity) sumquantity ,ps.unit,
sum (oub.net) sumnet, sum (oub.gross) sumgross, sum (oub.price) sumamount,oub.currency, ' 报关出区 '   as  billtype
from  outmemo om  join  outunitebefore oub 
on  om.outmemoid = oub.outmemoid  and  om.approve = ' 1 '   and  om.cancel = ' 0 '
     join  productstocks ps 
on  ps.productstocksid = oub.productstocksid 
where  om.corpid = @strCorpid   and   convert ( char ( 10 ),om.approvetime, 120 ) >= @StartDate  
and   convert ( char ( 10 ),om.approvetime, 120 ) <= @EndDate  
group   by  om.h883id,ps.oriproductid,ps.productpartno,ps.hscode,
ps.chinesename,ps.spec,ps.type,ps.unit,oub.currency)t1

insert   into   @verify
select   distinct  h883id  from  (
select  h883id  from  verify  where  corpid = @strcorpid   and  billtype = ' outmemo '
union   all  
select  h883id  from  verifydetail  where  corpid = @strcorpid   and  billtype = ' outmemo '
)t2

-- 获得@verify表中的记录数
select   @count = count ( * )  from   @verify
print   ' verify: ' + cast ( @count   as   char ( 10 )) + ' 条 '

-- 获得@outmemo表中的记录数
select   @count = count ( * )  from   @outmemo
print   ' outmemo: ' + cast ( @count   as   char ( 10 )) + ' 条 '

-- 取得两张表不一致的记录
-- 首先取得两张表中共同的数据
insert   into   @t1
select  v.h883id  from   @verify  v  join   @outmemo  o
on  o.h883id = v.h883id

-- 得到两张表中不一致的记录
select  h883id, ' verify '   as  billtype  from   @verify
where  h883id  not   in ( select  h883id  from   @t1 )
union   all
select  h883id, ' outmemo '   as  billtype  from   @outmemo
where  h883id  not   in ( select  h883id  from   @t1 )

使用了表变量，首先把需要比较的记录放到表变量中。然后根据表变量中的值查找出不一致的记录。

转载于:https://www.cnblogs.com/nerozhang/archive/2008/03/07/1094995.html