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)
使用了表变量，首先把需要比较的记录放到表变量中。然后根据表变量中的值查找出不一致的记录。
