oracle数据库库存比较,库存差异对比

---库存备份

select into fr_wmskcbak2018122210 from OPENQUERY(oracle, 'select from V_KC_DA600 ')

select into fr_spkfjcbak2018122210 from spkfjc

select into fr_sphwph2018122210 from sphwph where shl>0

--正常

select isnull(a.spid,b.spid) spid,isnull(a.shl,0) as WMSkc,a.pihao as wmspihao,isnull(b.shl,0) shl,b.pihao into #1

from (select spid,pihao,SUM(shl) as shl from sphwph where hw in ('HWI00000006','HWI00000003','HWI00000025','HWI00000002') group by spid,pihao) b

full join (select spid,ph pihao,SUM(SL_KCK) AS shl from fr_wmskcbak2018122210 where kb in ('BHK','ZJK','LHK') group by spid,ph) a on b.spid=a.spid and b.pihao=a.pihao

where isnull(a.shl,0)<>isnull(b.shl,0)

--Ｋ库

select isnull(a.spid,b.spid) spid,isnull(a.shl,0) as WMSkc,a.pihao as wmspihao,isnull(b.shl,0) shl,b.pihao into #2

from (select spid,pihao,SUM(shl) as shl from sphwph where hw in ('HWI00000028') group by spid,pihao) b

full join (select spid,ph pihao,SUM(SL_KCK) AS shl from fr_wmskcbak2018122210 where kb in ('KJK') group by spid,ph) a on b.spid=a.spid and b.pihao=a.pihao

where isnull(a.shl,0)<>isnull(b.shl,0)

--非正常库

select isnull(a.spid,b.spid) spid,isnull(a.shl,0) as WMSkc,a.pihao as wmspihao,isnull(b.shl,0) shl,b.pihao into #3

from (select spid,pihao,SUM(shl) as shl from sphwph where hw in ('HWI00000020','HWI00000022','HWI00000027','HWI00000030') group by spid,pihao) b

full join (select spid,ph pihao,SUM(SL_KC) AS shl from fr_wmskcbak2018122210 where kb in ('NHK','THK','CLK') group by spid,ph) a on b.spid=a.spid and b.pihao=a.pihao

where isnull(a.shl,0)<>isnull(b.shl,0)

--总库

select isnull(a.spid,b.spid) spid,isnull(a.shl,0) as WMSkc,a.pihao as wmspihao,isnull(b.shl,0) shl,b.pihao into #4

from (select spid,pihao,SUM(shl) as shl from sphwph group by spid,pihao) b

full join (select spid,ph pihao,SUM(SL_KC) AS shl from fr_wmskcbak2018122210 group by spid,ph) a on b.spid=a.spid and b.pihao=a.pihao

where isnull(a.shl,0)<>isnull(b.shl,0)

select a.spbh,a.spmch,a.shpgg,a.dw,a.jixing,a.jlgg,a.shpchd,convert(decimal(14,2),WMSkc) as 'WMS库存',convert(char(20),wmspihao) as 'wms批号',shl,pihao,isnull(convert(decimal(14,2),WMSkc),0)-isnull(shl,0) as '数量差异',(isnull(convert(decimal(14,2),WMSkc),0)-isnull(shl,0))*c.chbdj '差异金额'

from spkfk a,#3 b,spkfjc c where a.spid=b.spid and a.spid=c.spid and a.spbh not in ('J000324','J001359')

order by a.spbh

drop table #1,#2,#3,#4