给你一段类似的代码，方法有点笨，希望有好的方法。
CREATE PROCEDURE pzb_blp_tj
@panduan char(15),
@riqi1 char(15),
@riqi2 char(15)
AS
select a.chaizhi,
isnull(b.zongzl,0) as zongzl,isnull(b.zongsl,0) as zongsl,isnull(b.zongsj,0) as zongsj,isnull(b.zongje,0) as zongje,isnull(zongcs,0) as zongcs,
isnull(jc.jczl,0) as jczl,isnull(jc.jcsl,0) as jcsl,isnull(jc.jcsj,0) as jcsj,isnull(jc.jcje,0) as jcje,isnull(jc.jccs,0) as jccs,
isnull(xc.xczl,0) as xczl,isnull(xc.xcsl,0) as xcsl,isnull(xc.xcsj,0) as xcsj,isnull(xc.xcje,0) as xcje,isnull(xc.xccs,0) as xccs,
isnull(qzc.qzczl,0) as qzczl,isnull(qzc.qzcsl,0) as qzcsl,isnull(qzc.qzcsj,0) as qzcsj,isnull(qzc.qzcje,0) as qzcje,isnull(qzc.qzccs,0) as qzccs,
isnull(mc.mczl,0) as mczl,isnull(mc.mcsl,0) as mcsl,isnull(mc.mcsj,0) as mcsj,isnull(mc.mcje,0) as mcje,isnull(mc.mccs,0) as mccs,
isnull(bc.bczl,0) as bczl,isnull(bc.bcsl,0) as bcsl,isnull(bc.bcsj,0) as bcsj,isnull(bc.bcje,0) as bcje,isnull(bc.bccs,0) as bccs,
isnull(dj.djzl,0) as djzl,isnull(dj.djsl,0) as djsl,isnull(dj.djsj,0) as djsj,isnull(dj.djje,0) as djje,isnull(dj.djcs,0) as djcs,
isnull(ck.ckzl,0) as ckzl,isnull(ck.cksl,0) as cksl,isnull(ck.cksj,0) as cksj,isnull(ck.ckje,0) as ckje,isnull(ck.ckcs,0) as ckcs,
isnull(tk.tkzl,0) as tkzl,isnull(tk.tksl,0) as tksl,isnull(tk.tksj,0) as tksj,isnull(tk.tkje,0) as tkje,isnull(tk.tkcs,0) as tkcs,
isnull(jk.jkzl,0) as jkzl,isnull(jk.jksl,0) as jksl,isnull(jk.jksj,0) as jksj,isnull(jk.jkje,0) as jkje,isnull(jk.jkcs,0) as jkcs,
isnull(skz.skzzl,0) as skzzl,isnull(skz.skzsl,0) as skzsl,isnull(skz.skzsj,0) as skzsj,isnull(skz.skzje,0) as skzje,isnull(skz.skzcs,0) as skzcs,
isnull(hzc.hzczl,0) as hzczl,isnull(hzcsl,0) as hzcsl,isnull(hzcsj,0) as hzcsj,isnull(hzcje,0) as hzcje,isnull(hzccs,0) as hzccs,
isnull(czzl,0) as czzl,isnull(czsl,0) as czsl,isnull(czsj,0) as czsj,isnull(czje,0) as czje,isnull(czcs,0) as czcs,
isnull(jxzl,0) as jxzl,isnull(jxsl,0) as jxsl,isnull(jxsj,0) as jxsj,isnull(jxje,0) as jxje,isnull(jxcs,0) as jxcs,
isnull(zzzl,0) as zzzl,isnull(zzsl,0) as zzsl,isnull(zzsj,0) as zzsj,isnull(zzje,0) as zzje,isnull(zzcs,0) as zzcs,
isnull(gczl,0) as gczl,isnull(gcsl,0) as gcsl,isnull(gcsj,0) as gcsj,isnull(gcje,0) as gcje,isnull(gccs,0) as gccs,
isnull(lymzl,0) as lymzl,isnull(lymsl,0) as lymsl,isnull(lymsj,0) as lymsj,isnull(lymje,0) as lymje,isnull(lymcs,0) as lymcs,
isnull(wymzl,0) as wymzl,isnull(wymsl,0) as wymsl,isnull(wymsj,0) as wymsj,isnull(wymje,0) as wymje,isnull(wymcs,0) as wymcs,
isnull(cczl,0) as cczl,isnull(ccsl,0) as ccsl,isnull(ccsj,0) as ccsj,isnull(ccje,0) as ccje,isnull(cccs,0) as cccs,
isnull(qczl,0) as qczl,isnull(qcsl,0) as qcsl,isnull(qcsj,0) as qcsj,isnull(qcje,0) as qcje,isnull(qccs,0) as qccs,
isnull(jhzl,0) as jhzl,isnull(jhsl,0) as jhsl,isnull(jhsj,0) as jhsj,isnull(jhje,0) as jhje,isnull(jhcs,0) as jhcs,
isnull(gcbzl,0) as gcbzl,isnull(gcbsl,0) as gcbsl,isnull(gcbsj,0) as gcbsj,isnull(gcbje,0) as gcbje,isnull(gcbcs,0) as gcbcs,
isnull(yyzl,0) as yyzl,isnull(yysl,0) as yysl,isnull(yysj,0) as yysj,isnull(yyje,0) as yyje,isnull(yycs,0) as yycs,
isnull(hqzl,0) as hqzl,isnull(hqsl,0) as hqsl,isnull(hqsj,0) as hqsj,isnull(hqje,0) as hqje,isnull(hqcs,0) as hqcs
from pzb_cz a
left join --材质总重量
( select chaizhi,sum(isnull(weight,0)) as zongzl,sum(isnull(qty,0)) as zongsl,sum(isnull(fgsj,0)) as zongsj,sum(isnull(money1,0)) as zongje,count(*) as zongcs
from pzb_blp
where c>=@riqi1 and
c<=@riqi2 and
wenti=@panduan
group by chaizhi
) as b on a.chaizhi=b.chaizhi
left join
( select chaizhi,sum(isnull(weight,0)) as jczl,sum(isnull(qty,0)) as jcsl,sum(isnull(fgsj,0)) as jcsj,sum(isnull(money1,0)) as jcje,count(*) as jccs
from pzb_blp
where c>=@riqi1 and
c<=@riqi2 and
bumeng='锯床' and
wenti=@panduan
group by chaizhi
) as jc on a.chaizhi=jc.chaizhi
left join
( select chaizhi,sum(isnull(weight,0)) as xczl,sum(isnull(qty,0)) as xcsl,sum(isnull(fgsj,0)) as xcsj,sum(isnull(money1,0)) as xcje,count(*) as xccs
from pzb_blp
where c>=@riqi1 and
c<=@riqi2 and
bumeng='铣床' and
wenti=@panduan
group by chaizhi
) as xc on a.chaizhi=xc.chaizhi
left join
( select chaizhi,sum(isnull(weight,0)) as qzczl,sum(isnull(qty,0)) as qzcsl,sum(isnull(fgsj,0)) as qzcsj,sum(isnull(money1,0)) as qzcje,count(*) as qzccs
from pzb_blp
where c>=@riqi1 and
c<=@riqi2 and
bumeng='前钻床' and
wenti=@panduan
group by chaizhi
) as qzc on a.chaizhi=qzc.chaizhi
left join
( select chaizhi,sum(isnull(weight,0)) as mczl,sum(isnull(qty,0)) as mcsl,sum(isnull(fgsj,0)) as mcsj,sum(isnull(money1,0)) as mcje,count(*) as mccs
from pzb_blp
where c>=@riqi1 and
c<=@riqi2 and
bumeng='磨床' and
wenti=@panduan
group by chaizhi
) as mc on a.chaizhi=mc.chaizhi
left join
( select chaizhi,sum(isnull(weight,0)) as bczl,sum(isnull(qty,0)) as bcsl,sum(isnull(fgsj,0)) as bcsj,sum(isnull(money1,0)) as bcje,count(*) as bccs
from pzb_blp
where c>=@riqi1 and
c<=@riqi2 and
bumeng='编程' and
wenti=@panduan
group by chaizhi
) as bc on a.chaizhi=bc.chaizhi
left join
( select chaizhi,sum(isnull(weight,0)) as djzl,sum(isnull(qty,0)) as djsl,sum(isnull(fgsj,0)) as djsj,sum(isnull(money1,0)) as djje,count(*) as djcs
from pzb_blp
where c>=@riqi1 and
c<=@riqi2 and
bumeng='刀具库' and
wenti=@panduan
group by chaizhi
) as dj on a.chaizhi=dj.chaizhi
left join
( select chaizhi,sum(isnull(weight,0)) as ckzl,sum(isnull(qty,0)) as cksl,sum(isnull(fgsj,0)) as cksj,sum(isnull(money1,0)) as ckje,count(*) as ckcs
from pzb_blp
where c>=@riqi1 and
c<=@riqi2 and
bumeng='粗框' and
wenti=@panduan
group by chaizhi
) as ck on a.chaizhi=ck.chaizhi
left join
( select chaizhi,sum(isnull(weight,0)) as tkzl,sum(isnull(qty,0)) as tksl,sum(isnull(fgsj,0)) as tksj,sum(isnull(money1,0)) as tkje,count(*) as tkcs
from pzb_blp
where c>=@riqi1 and
c<=@riqi2 and
bumeng='镗孔' and
wenti=@panduan
group by chaizhi
) as tk on a.chaizhi=tk.chaizhi
left join
( select chaizhi,sum(isnull(weight,0)) as jkzl,sum(isnull(qty,0)) as jksl,sum(isnull(fgsj,0)) as jksj,sum(isnull(money1,0)) as jkje,count(*) as jkcs
from pzb_blp
where c>=@riqi1 and
c<=@riqi2 and
bumeng='精框' and
wenti=@panduan
group by chaizhi
) as jk on a.chaizhi=jk.chaizhi
left join
( select chaizhi,sum(isnull(weight,0)) as skzzl,sum(isnull(qty,0)) as skzsl,sum(isnull(fgsj,0)) as skzsj,sum(isnull(money1,0)) as skzje,count(*) as skzcs
from pzb_blp
where c>=@riqi1 and
c<=@riqi2 and
bumeng='深孔钻' and
wenti=@panduan
group by chaizhi
) as skz on a.chaizhi=skz.chaizhi
left join
( select chaizhi,sum(isnull(weight,0)) as hzczl,sum(isnull(qty,0)) as hzcsl,sum(isnull(fgsj,0)) as hzcsj,sum(isnull(money1,0)) as hzcje,count(*) as hzccs
from pzb_blp
where c>=@riqi1 and
c<=@riqi2 and
bumeng='后钻床' and
wenti=@panduan
group by chaizhi
) as hzc on a.chaizhi=hzc.chaizhi
left join
( select chaizhi,sum(isnull(weight,0)) as czzl,sum(isnull(qty,0)) as czsl,sum(isnull(fgsj,0)) as czsj,sum(isnull(money1,0)) as czje,count(*) as czcs
from pzb_blp
where c>=@riqi1 and
c<=@riqi2 and
bumeng='粗装' and
wenti=@panduan
group by chaizhi
) as cz on a.chaizhi=cz.chaizhi
left join
( select chaizhi,sum(isnull(weight,0)) as jxzl,sum(isnull(qty,0)) as jxsl,sum(isnull(fgsj,0)) as jxsj,sum(isnull(money1,0)) as jxje,count(*) as jxcs
from pzb_blp
where c>=@riqi1 and
c<=@riqi2 and
bumeng='精铣' and
wenti=@panduan
group by chaizhi
) as jx on a.chaizhi=jx.chaizhi
left join
( select chaizhi,sum(isnull(weight,0)) as zzzl,sum(isnull(qty,0)) as zzsl,sum(isnull(fgsj,0)) as zzsj,sum(isnull(money1,0)) as zzje,count(*) as zzcs
from pzb_blp
where c>=@riqi1 and
c<=@riqi2 and
bumeng='总装' and
wenti=@panduan
group by chaizhi
) as zz on a.chaizhi=zz.chaizhi
left join
( select chaizhi,sum(isnull(weight,0)) as gczl,sum(isnull(qty,0)) as gcsl,sum(isnull(fgsj,0)) as gcsj,sum(isnull(money1,0)) as gcje,count(*) as gccs
from pzb_blp
where c>=@riqi1 and
c<=@riqi2 and
bumeng='钢材' and
wenti=@panduan
group by chaizhi
) as gc on a.chaizhi=gc.chaizhi
left join
( select chaizhi,sum(isnull(weight,0)) as lymzl,sum(isnull(qty,0)) as lymsl,sum(isnull(fgsj,0)) as lymsj,sum(isnull(money1,0)) as lymje,count(*) as lymcs
from pzb_blp
where c>=@riqi1 and
c<=@riqi2 and
bumeng='内圆磨床' and
wenti=@panduan
group by chaizhi
) as lym on a.chaizhi=lym.chaizhi
left join
( select chaizhi,sum(isnull(weight,0)) as wymzl,sum(isnull(qty,0)) as wymsl,sum(isnull(fgsj,0)) as wymsj,sum(isnull(money1,0)) as wymje,count(*) as wymcs
from pzb_blp
where c>=@riqi1 and
c<=@riqi2 and
bumeng='外圆磨床' and
wenti=@panduan
group by chaizhi
) as wym on a.chaizhi=wym.chaizhi
left join
( select chaizhi,sum(isnull(weight,0)) as cczl,sum(isnull(qty,0)) as ccsl,sum(isnull(fgsj,0)) as ccsj,sum(isnull(money1,0)) as ccje,count(*) as cccs
from pzb_blp
where c>=@riqi1 and
c<=@riqi2 and
bumeng='车床' and
wenti=@panduan
group by chaizhi
) as cc on a.chaizhi=cc.chaizhi
left join
( select chaizhi,sum(isnull(weight,0)) as qczl,sum(isnull(qty,0)) as qcsl,sum(isnull(fgsj,0)) as qcsj,sum(isnull(money1,0)) as qcje,count(*) as qccs
from pzb_blp
where c>=@riqi1 and
c<=@riqi2 and
bumeng='QC' and
wenti=@panduan
group by chaizhi
) as qc on a.chaizhi=qc.chaizhi
left join
( select chaizhi,sum(isnull(weight,0)) as jhzl,sum(isnull(qty,0)) as jhsl,sum(isnull(fgsj,0)) as jhsj,sum(isnull(money1,0)) as jhje,count(*) as jhcs
from pzb_blp
where c>=@riqi1 and
c<=@riqi2 and
bumeng='计划' and
wenti=@panduan
group by chaizhi
) as jh on a.chaizhi=jh.chaizhi
left join
( select chaizhi,sum(isnull(weight,0)) as gcbzl,sum(isnull(qty,0)) as gcbsl,sum(isnull(fgsj,0)) as gcbsj,sum(isnull(money1,0)) as gcbje,count(*) as gcbcs
from pzb_blp
where c>=@riqi1 and
c<=@riqi2 and
bumeng='工程' and
wenti=@panduan
group by chaizhi
) as gcb on a.chaizhi=gcb.chaizhi
left join
( select chaizhi,sum(isnull(weight,0)) as yyzl,sum(isnull(qty,0)) as yysl,sum(isnull(fgsj,0)) as yysj,sum(isnull(money1,0)) as yyje,count(*) as yycs
from pzb_blp
where c>=@riqi1 and
c<=@riqi2 and
bumeng='营业' and
wenti=@panduan
group by chaizhi
) as yy on a.chaizhi=yy.chaizhi
left join
( select chaizhi,sum(isnull(weight,0)) as hqzl,sum(isnull(qty,0)) as hqsl,sum(isnull(fgsj,0)) as hqsj,sum(isnull(money1,0)) as hqje,count(*) as hqcs
from pzb_blp
where c>=@riqi1 and
c<=@riqi2 and
bumeng='后勤' and
wenti=@panduan
group by chaizhi
) as hq on a.chaizhi=hq.chaizhi
order by a.chaizhi
GO
]