我还是全发出来吧:select q.日期1, q.产品名称1, q.编码1, q.厚度1, sum(q.正品数1)+sum(q.短尺数1)+sum(q.次品数1), sum(q.正品重1)+sum(q.短尺重1)+sum(q.次品重1) from( select zp.shijian1 as 日期1, zp.b1 as 产品名称1, zp.a1 as 编码1, zp.c1 as 厚度1, to_number(zp.sla1) as 正品重1, zp.slb1 as 正品数1, dc.sla2 as 短尺重1, dc.slb2 as 短尺数1, cp.sla3 as 次品重1, cp.slb3 as 次品数1--sum(zp.slb1)+sum(dc.slb2)+sum(cp.slb3) qq from ( select wl.fnumber as a1,wl.fname_l2 as b1,sj.sla as sla1 ,sj.slb as slb1,sj.fthickness as c1,sj.dos1 as shijian1 from ( select sum(bb.FPiece) as slb,sum(bb.FFactQty) as sla,bb.fnewmaterialid,bb.FThickness,aa.das1 as dos1 from ( select to_char(dt.fbizdate,'yyyy-mm-dd') as das1,fl.fid,dt.fnumber,fl.FThickness from dl.T_ST_StorageForCheck dt left join dl.T_ST_StorageForCheckentry fl on dt.fid=fl.fparentid where dt.FBillStatus = 4 and to_char(dt.fbizdate,'yyyy-mm-dd')>='2011-08-01' and to_char(dt.fbizdate,'yyyy-mm-dd')<='2011-08-07' ) aa left join dl.T_ST_StorageForCheckDetail bb on aa.fid=bb.fparentid group by aa.das1,bb.fnewmaterialid,bb.FThickness order by aa.das1,bb.fnewmaterialid,bb.FThickness ) sj left join dl.t_bd_material wl on wl.fid=sj.fnewmaterialid where fnumber like '4.2.1%' --and fnumber = '${cp}' or '${cp}' is null ) zp left join ( select wl.fnumber as a2,wl.fname_l2 as b2,sj.sla as sla2,sj.slb as slb2,sj.fthickness as c2,sj.dos2 as shijian2 from ( select sum(bb.FPiece) as slb,sum(bb.FFactQty) as sla,bb.fnewmaterialid,bb.FThickness,aa.das2 as dos2 from ( select to_char(dt.fbizdate,'yyyy-mm-dd') as das2,fl.fid,dt.fnumber,fl.FThickness from dl.T_ST_StorageForCheck dt left join dl.T_ST_StorageForCheckentry fl on dt.fid=fl.fparentid where dt.FBillStatus = 4 and to_char(dt.fbizdate,'yyyy-mm-dd')>='2011-08-01' and to_char(dt.fbizdate,'yyyy-mm-dd')<='2011-08-07' ) aa left join dl.T_ST_StorageForCheckDetail bb on aa.fid=bb.fparentid group by aa.das2,bb.fnewmaterialid,bb.FThickness order by aa.das2,bb.fnewmaterialid,bb.FThickness ) sj left join dl.t_bd_material wl on wl.fid=sj.fnewmaterialid where fnumber like '4.2.3%' ) dc on zp.b1||'短尺'=dc.b2 and zp.c1=dc.c2 and zp.shijian1 = dc.shijian2left join ( select wl.fnumber as a3,wl.fname_l2 as b3,sj.sla as sla3,sj.slb as slb3,sj.fthickness as c3,sj.dos3 as shijian3 from ( select sum(bb.FPiece) as slb,sum(bb.FFactQty) as sla,bb.fnewmaterialid,bb.FThickness,aa.das3 as dos3 from ( select to_char(dt.fbizdate,'yyyy-mm-dd') as das3,fl.fid,dt.fnumber,fl.FThickness from dl.T_ST_StorageForCheck dt left join dl.T_ST_StorageForCheckentry fl on dt.fid=fl.fparentid where dt.FBillStatus = 4 and to_char(dt.fbizdate,'yyyy-mm-dd')>='2011-08-01' and to_char(dt.fbizdate,'yyyy-mm-dd')<='2011-08-07' ) aa left join dl.T_ST_StorageForCheckDetail bb on aa.fid=bb.fparentid group by aa.das3,bb.fnewmaterialid,bb.FThickness order by aa.das3,bb.fnewmaterialid,bb.FThickness ) sj left join dl.t_bd_material wl on wl.fid=sj.fnewmaterialid where fnumber like '4.2.2%' ) cp on zp.b1||'次品'=cp.b3 and zp.c1=cp.c3 and zp.shijian1 = cp.shijian3 ) q group by q.日期1,q.产品名称1,q.编码1,q.厚度1 不出数,你们帮忙看看那的问题吧,谢谢
select a,b+c+d as SUM_ALL from tb1
q.产品名称1,
q.编码1,
q.厚度1,
sum(q.正品数1)+sum(q.短尺数1)+sum(q.次品数1),
sum(q.正品重1)+sum(q.短尺重1)+sum(q.次品重1)
from(
select
zp.shijian1 as 日期1,
zp.b1 as 产品名称1,
zp.a1 as 编码1,
zp.c1 as 厚度1,
to_number(zp.sla1) as 正品重1,
zp.slb1 as 正品数1,
dc.sla2 as 短尺重1,
dc.slb2 as 短尺数1,
cp.sla3 as 次品重1,
cp.slb3 as 次品数1--sum(zp.slb1)+sum(dc.slb2)+sum(cp.slb3) qq from
(
select wl.fnumber as a1,wl.fname_l2 as b1,sj.sla as sla1 ,sj.slb as slb1,sj.fthickness as c1,sj.dos1 as shijian1 from
(
select sum(bb.FPiece) as slb,sum(bb.FFactQty) as sla,bb.fnewmaterialid,bb.FThickness,aa.das1 as dos1 from
(
select to_char(dt.fbizdate,'yyyy-mm-dd') as das1,fl.fid,dt.fnumber,fl.FThickness from dl.T_ST_StorageForCheck dt left join dl.T_ST_StorageForCheckentry fl on dt.fid=fl.fparentid
where dt.FBillStatus = 4 and to_char(dt.fbizdate,'yyyy-mm-dd')>='2011-08-01'
and to_char(dt.fbizdate,'yyyy-mm-dd')<='2011-08-07'
) aa
left join dl.T_ST_StorageForCheckDetail bb on aa.fid=bb.fparentid group by aa.das1,bb.fnewmaterialid,bb.FThickness order by aa.das1,bb.fnewmaterialid,bb.FThickness
) sj
left join dl.t_bd_material wl on wl.fid=sj.fnewmaterialid where fnumber like '4.2.1%' --and fnumber = '${cp}' or '${cp}' is null
) zp
left join
(
select wl.fnumber as a2,wl.fname_l2 as b2,sj.sla as sla2,sj.slb as slb2,sj.fthickness as c2,sj.dos2 as shijian2 from
(
select sum(bb.FPiece) as slb,sum(bb.FFactQty) as sla,bb.fnewmaterialid,bb.FThickness,aa.das2 as dos2 from
(
select to_char(dt.fbizdate,'yyyy-mm-dd') as das2,fl.fid,dt.fnumber,fl.FThickness from dl.T_ST_StorageForCheck dt left join dl.T_ST_StorageForCheckentry fl on dt.fid=fl.fparentid
where dt.FBillStatus = 4 and to_char(dt.fbizdate,'yyyy-mm-dd')>='2011-08-01'
and to_char(dt.fbizdate,'yyyy-mm-dd')<='2011-08-07'
) aa
left join dl.T_ST_StorageForCheckDetail bb on aa.fid=bb.fparentid group by aa.das2,bb.fnewmaterialid,bb.FThickness order by aa.das2,bb.fnewmaterialid,bb.FThickness
) sj
left join dl.t_bd_material wl on wl.fid=sj.fnewmaterialid where fnumber like '4.2.3%'
) dc
on zp.b1||'短尺'=dc.b2 and zp.c1=dc.c2 and zp.shijian1 = dc.shijian2left join
(
select wl.fnumber as a3,wl.fname_l2 as b3,sj.sla as sla3,sj.slb as slb3,sj.fthickness as c3,sj.dos3 as shijian3 from
(
select sum(bb.FPiece) as slb,sum(bb.FFactQty) as sla,bb.fnewmaterialid,bb.FThickness,aa.das3 as dos3 from
(
select to_char(dt.fbizdate,'yyyy-mm-dd') as das3,fl.fid,dt.fnumber,fl.FThickness from dl.T_ST_StorageForCheck dt left join dl.T_ST_StorageForCheckentry fl on dt.fid=fl.fparentid
where dt.FBillStatus = 4 and to_char(dt.fbizdate,'yyyy-mm-dd')>='2011-08-01'
and to_char(dt.fbizdate,'yyyy-mm-dd')<='2011-08-07'
) aa
left join dl.T_ST_StorageForCheckDetail bb on aa.fid=bb.fparentid group by aa.das3,bb.fnewmaterialid,bb.FThickness order by aa.das3,bb.fnewmaterialid,bb.FThickness
) sj
left join dl.t_bd_material wl on wl.fid=sj.fnewmaterialid where fnumber like '4.2.2%'
) cp
on zp.b1||'次品'=cp.b3 and zp.c1=cp.c3 and zp.shijian1 = cp.shijian3
) q
group by q.日期1,q.产品名称1,q.编码1,q.厚度1
不出数,你们帮忙看看那的问题吧,谢谢
q.正品重1+q.短尺重1+q.次品重1
这样写也不行啊
sum(q.正品数1)+sum(q.短尺数1)+sum(q.次品数1),改为isnull(sum(q.正品数1),0)+isnull(sum(q.短尺数1),0)+isnull(sum(q.次品数1),0),