求助。sum函数汇总数字出现无限小数
请教各位,我用做库存的时候用sum函数汇总库存数量,回出现无限小数的情况,求助各位有没有遇到过,如何解决?
select row_number() over(order by getdate()) sid,
b.project_id, b.storage_sid, b.storage_code, b.storage_name, b.parent_id, b.display_id,
a.mac_sid as mat_sid, a.sa_wzrk_zb_mc as title, a.sa_wzrk_zb_ggxh, a.hd as model, a.sa_wzrk_zb_dw as unitname,a.ghdw,a.kcsl as kcsl,a.dj,a.fcsl,
a.srsl,a.tksl,a.thsl,ISNULL(isnull(a.kcsl,0)*isnull(a.dj,0),0) as zj,ISNULL(isnull(a.srsl,0)*isnull(a.dj,0),0) as srzj,
ISNULL(isnull(a.fcsl,0)*isnull(a.dj,0),0) as fczj,ISNULL(isnull(a.tksl,0)*isnull(a.dj,0),0) as tkzj ,ISNULL(isnull(a.thsl,0)*isnull(a.dj,0),0) as thzj,
isnull(ISNULL(a.srsl,0)*isnull(a.bhsdj,0),0) as bhssrje,isnull(ISNULL(a.fcsl,0)*isnull(a.bhsdj,0),0) as bhsfcje,isnull(ISNULL(a.tksl,0)*isnull(a.bhsdj,0),0) as bhstkje,
isnull(ISNULL(a.thsl,0)*isnull(a.bhsdj,0),0) as bhsthje,isnull(ISNULL(a.kcsl,0)*isnull(a.bhsdj,0),0) as bhskcje,a.bhsdj,a.szc,a.sfzzs
from (
select a.drck_sid, a.mac_sid,sum(a.fcsl) as fcsl,sum(a.srsl) as srsl,sum(a.tksl) as tksl,a.ghdw ,a.sa_wzrk_zb_mc,a.sa_wzrk_zb_ggxh,a.hd,a.sa_wzrk_zb_dw,sum(a.kcsl) kcsl,SUM(a.thsl) as thsl,a.dj,a.bhsdj,a.szc,a.sfzzs from(
--材料入库
select y.drck_sid, x.mac_sid,x.ghdw,x.sa_wzrk_zb_mc,x.sa_wzrk_zb_ggxh,x.hd,x.sa_wzrk_zb_dw,isnull(0,0)as fcsl,isnull(x.rksl,0) as srsl,
isnull(x.rksl,0)as kcsl,isnull(0,0) as tksl,isnull(x.sa_wzrk_zb_dj,0) as dj,isnull(x.hsdj,0) as bhsdj,ISNULL(0,0) as thsl,x.szc,x.sfzzs from
(select b.ghdw,a.pinpai,b.mac_sid,isnull(b.rksl,0) as rksl,b.sa_wzrk_zb_dj,a.m_sid,b.sa_wzrk_zb_mc,
b.sa_wzrk_zb_ggxh,b.hd,b.sa_wzrk_zb_dw,b.hsdj,b.szc,b.sfzzs from sa_wzrk_zb1 a
left join sa_wzrk_zb b
on a.sa_wzrk_zb1_sid=b.sa_wzrk_sid) x
left join sa_wzrk y on x.m_sid=y.sa_wzrk_sid where y.Status='批准' and y.Y1='材料入库'
union all
--材料调拨
select a.dcck_sid, b.mat_sid,b.ghdw,b.mc,b.gg,b.xh,b.dw,isnull(b.sl, 0) as fcsl ,isnull(0,0)as srsl,-isnull(b.sl, 0) as kcsl,
isnull(0,0) as tksl,ISNULL(b.dj,0) as dj,ISNULL(b.bhsdj,0) as bhsdj,ISNULL(0,0) as thsl,b.szc,b.zzs
from sa_wzdb a inner join sa_wzdb_zb b
on a.sa_wzdb_sid = b.sa_wzdb_sid
where a.status = '批准'
union all
select a.drck_sid, b.mat_sid,b.ghdw,b.mc,b.gg,b.xh,b.dw,isnull(0,0)as fcsl,isnull(b.sl, 0) as srsl ,isnull(b.sl, 0) as kcsl,
isnull(0,0) as tksl,ISNULL(b.dj,0) as dj,ISNULL(b.bhsdj,0) as bhsdj,ISNULL(0,0) as thsl,b.szc,b.zzs
from sa_wzdb a inner join sa_wzdb_zb b
on a.sa_wzdb_sid = b.sa_wzdb_sid
where a.status = '批准'
union all
--物资领用
select a.dcck_sid, b.mat_sid,b.ghdw,b.sa_wzly_zb_mc,b.sa_wzly_zb_xhgg,b.Y4,b.sa_wzly_zb_dw,isnull(b.sa_wzly_zb_sfsl, 0) as fcsl,isnull(0,0)as srsl ,
-isnull(b.sa_wzly_zb_sfsl, 0) as kcsl, isnull(0,0) as tksl,
ISNULL(b.sa_wzly_zb_dj,0) as dj,ISNULL(b.bhsdj,0) as bhsdj,ISNULL(0,0) as thsl,b.szc,b.zzs
from sa_wzly a inner join sa_wzly_zb b
on a.sa_wzly_sid = b.sa_wzly_sid
where a.status = '批准' and a.Y2='材料领用'
union all
--材料退库
select a.ck_sid,b.mat_sid,b.ghdw,b.wzmc,b.gg,b.hd,b.dw,-isnull(b.tknum,0)as fc,isnull(0,0)as srsl,isnull(b.tknum,0)as kcsl,
isnull(b.tknum,0) as tksl,ISNULL(b.dj,0) as dj,ISNULL(b.bhsdj,0) as bhsdj,ISNULL(0,0) as thsl,b.szc,b.zzs
from sa_cltk a inner join sa_cltk_detail b
on a.sid=b.m_sid where a.status='批准' and a.type='材料'
union all
--材料退货
select a.ck_sid,b.mat_sid,b.gys,b.wzmc,b.gg,b.xh,b.dw,ISNULL(0,0) as fc,ISNULL(0,0) as srsl,-ISNULL(b.sl,0) as kcsl,
ISNULL(0,0) as tksl ,ISNULL(b.dj,0) as dj,ISNULL(b.bhsdj,0) as bhsdj,ISNULL(b.sl,0) as thsl,b.zc,b.zzs
from sa_wzth a inner join sa_wzth_zb b on a.zb_sid=b.zb_sid where a.status='批准' and a.type='材料'
) a
group by a.mac_sid,a.drck_sid,a.dj,a.sa_wzrk_zb_ggxh,a.ghdw,a.hd,a.sa_wzrk_zb_mc,a.sa_wzrk_zb_dw,a.bhsdj,a.szc,a.sfzzs
) a
inner join mat_storage b on a.drck_sid = b.storage_sid