CREATE procedure kufangChuRuKuReport_KUCUN_bgy @FromTime varchar(50), @ToTime varchar(50), @czy varchar(50) as begin set nocount on declare @sjcdrq varchar(50)select @sjcdrq=convert(varchar(4),year(riqi))+'-'+convert(varchar(2),month(riqi))+'-'+convert(varchar(2),day(riqi)) from kf_wlcundang where year(riqi)=year(@fromtime) and month(riqi)=month(@fromtime) and (bgybh=@czy) select kfbh,bm,lbjdh,sum(inCount) as inCount,sum(inPrice) as inPrice into #tempbillinA from (select * from (select tpindc.lbjdh,tpindc.bm,tpindc.kfbh, sum(tpindc.rksl) as inCount, sum(tp1.jhjg*tpindc.rksl) as inPrice from kf_billincundang tpinc left join kf_billindetailcundang tpindc on tpinc.rkdh=tpinDc.rkdh left join kf_bgyqcsj_view tp1 on tpinDc.lbjdh=tp1.lbjdh and tpindc.bm=tp1.bm and tpindc.kfbh=tp1.kfbh where (tpinc.rkrq >= @FromTime and tpinc.rkrq <= @ToTime) and (tp1.bgybh=@czy) group by tpinDc.lbjdh,tpindc.bm,tpindc.kfbh union all -- #tempbillinc select tpind.lbjdh,tpind.bm,tpind.kfbh, sum(tpind.rksl) as inCount, sum(tp1.jhjg*tpind.rksl) as inPrice from kf_billin tpin left join kf_billindetail tpind on tpin.rkdh=tpinD.rkdh left join kf_bgyqcsj_view tp1 on tpinD.lbjdh=tp1.lbjdh and tpind.bm=tp1.bm and tpind.kfbh=tp1.kfbh where (tpin.rkrq >= @FromTime and tpin.rkrq <= @ToTime) and (tp1.bgybh=@czy) group by tpinD.lbjdh,tpind.bm,tpind.kfbh) tempbillinAll) tempbillinA group by bm,lbjdh,kfbh select * into #tempbilloutAll from ( select outd.bm,outd.lbjdh,outd.kfbh, sum(outd.cksl) as outCount, sum(tp1.jhjg*outd.cksl) as outPrice from kf_billout bout left join kf_billoutdetail outd on bout.ckdh=outd.ckdh left join kf_bgyqcsj_view tp1 on outd.lbjdh=tp1.lbjdh and outd.bm=tp1.bm and outd.kfbh=tp1.kfbh where (bout.ckrq >= @FromTime and bout.ckrq <= @ToTime) and (tp1.bgybh=@czy) group by outd.lbjdh,outd.bm,outd.kfbh union all select outdc.bm,outdc.lbjdh,outdc.kfbh, sum(outdc.cksl) as outCount, sum(tp1.jhjg*outdc.cksl) as outPrice from kf_billoutcundang boutc left join kf_billoutdetailcundang outdc on boutc.ckdh=outdc.ckdh left join kf_bgyqcsj_view tp1 on outdc.lbjdh=tp1.lbjdh and outdc.bm=tp1.bm and outdc.kfbh=tp1.kfbh where (boutc.ckrq >= @FromTime and boutc.ckrq <= @ToTime) and (tp1.bgybh=@czy) group by outdc.lbjdh,outdc.bm,outdc.kfbh) tempbilloutAll select bm,lbjdh,BGYBH,kfbh,jhjg into #tempwl from kf_bgyqcsj_view where (bgybh=@czy)--#tempbillinout+#tempwlQC+#tempbillOutA +#tempbillinA=#TEMPALL select wl.kfbh,wl.bm,wl.lbjdh,QCnum=case tempwlQC.kcsl when null then 0 else tempwlQC.kcsl end,wl.jhjg,inCount=case TPI.inCount when null then 0 else TPI.inCount end, inPrice=case TPI.inPrice when null then 0 else TPI.inPrice end, outCount=case TPA.outCount when null then 0 else TPA.outCount end, outPrice=case TPA.outPrice when null then 0 else TPA.outPrice end into #tempALL from #tempwl wl left join (select * from kf_wlcundang where (RiQi=@sjcdrq) and (bgybh=@czy)) tempwlQC on (wl.kfbh =tempwlQC.kfbh and wl.bm=tempwlQC.bm AND wl.lbjdh=tempwlQC.lbjdh) left join #tempbillinA TPI on wl.kfbh=tpi.kfbh and wl.bm=TPI.bm and wl.LBJDH=TPI.LBJDH left join (select kfbh,bm,lbjdh,sum(outCount) as outCount,sum(outPrice) as outPrice from #tempbilloutAll group by bm,lbjdh,kfbh) TPA on wl.kfbh=tpa.kfbh and wl.BM=TPA.BM AND wl.LBJDH=TPA.LBJDH select #tempwl.kfbh,kf.kfmc,#tempwl.bm,gys.gysmc,#tempwl.lbjdh,wldy.lbjmc,wldy.ggxh, wldy.dw,zhxx.shlx,zhxx.sfjcj,#tempALL.QCnum,#tempALL.jhjg,#tempALL.inCount,#tempALL.inPrice, #tempALL.outCount,#tempALL.outPrice,(#tempALL.QCnum+#tempALL.inCount-#tempALL.outCount) as StoreAmount,(#tempALL.QCnum+#tempALL.inCount-#tempALL.outCount)*#tempALL.jhjg as StorePrice from #tempwl left join #tempALL on (#tempwl.kfbh=#tempall.kfbh and #tempwl.bm=#tempall.bm and #tempwl.lbjdh=#tempall.lbjdh) left join bomwldy wldy on #tempwl.lbjdh=wldy.lbjdh left join bomgysxx gys on #tempwl.bm=gys.bm left join kf_wlkfzhxx zhxx on #tempwl.lbjdh=zhxx.lbjdh and #tempwl.kfbh=zhxx.kfbh left join kf_kfxx kf on #tempwl.kfbh=kf.kfbh order by #tempwl .bm ,#tempwl .lbjdh drop table #tempwl --drop table #tempbilloutAll drop table #tempbillinA drop table #tempALL --set nocount off end GO
OLD:
select @sjcdrq=convert(varchar(4),year(riqi))+'-'+convert(varchar(2),month(riqi))+'-'+convert(varchar(2),day(riqi))
NEW:
selectd @sjcdrq=convert( varchar(10),riqi,120)其他的没往下看了, 不好意系
给你提个建议!,比如convert一个日期到yyyy-mm-dd的字符可用convert(varchar(10),date,120),到格式yyyymmdd用convert(varchar(8),date,112)
select *尽量少用,除非你真的需要这么多fields,其次!使用这么多tempdb有些慢!
select @变量=字段值...最好能确定只有一条记录符合条件,否则还是加上top 1好些。
这么多的left join都是可以优化的,特别是tempdb都可以优化的
@FromTime varchar(50),
@ToTime varchar(50),
@czy varchar(50)
as
begin
set nocount on
declare @sjcdrq varchar(50)select @sjcdrq=convert(varchar(4),year(riqi))+'-'+convert(varchar(2),month(riqi))+'-'+convert(varchar(2),day(riqi))
from kf_wlcundang where year(riqi)=year(@fromtime) and month(riqi)=month(@fromtime) and (bgybh=@czy)
select kfbh,bm,lbjdh,sum(inCount) as inCount,sum(inPrice) as inPrice into #tempbillinA
from (select * from (select tpindc.lbjdh,tpindc.bm,tpindc.kfbh,
sum(tpindc.rksl) as inCount,
sum(tp1.jhjg*tpindc.rksl) as inPrice
from kf_billincundang tpinc left join kf_billindetailcundang tpindc on tpinc.rkdh=tpinDc.rkdh
left join kf_bgyqcsj_view tp1 on tpinDc.lbjdh=tp1.lbjdh and tpindc.bm=tp1.bm and tpindc.kfbh=tp1.kfbh
where (tpinc.rkrq >= @FromTime and tpinc.rkrq <= @ToTime) and (tp1.bgybh=@czy)
group by tpinDc.lbjdh,tpindc.bm,tpindc.kfbh
union all -- #tempbillinc
select tpind.lbjdh,tpind.bm,tpind.kfbh,
sum(tpind.rksl) as inCount,
sum(tp1.jhjg*tpind.rksl) as inPrice
from kf_billin tpin left join kf_billindetail tpind on tpin.rkdh=tpinD.rkdh
left join kf_bgyqcsj_view tp1 on tpinD.lbjdh=tp1.lbjdh and tpind.bm=tp1.bm and tpind.kfbh=tp1.kfbh
where (tpin.rkrq >= @FromTime and tpin.rkrq <= @ToTime) and (tp1.bgybh=@czy)
group by tpinD.lbjdh,tpind.bm,tpind.kfbh) tempbillinAll) tempbillinA
group by bm,lbjdh,kfbh
select * into #tempbilloutAll from ( select outd.bm,outd.lbjdh,outd.kfbh,
sum(outd.cksl) as outCount,
sum(tp1.jhjg*outd.cksl) as outPrice
from kf_billout bout left join kf_billoutdetail outd on bout.ckdh=outd.ckdh
left join kf_bgyqcsj_view tp1 on outd.lbjdh=tp1.lbjdh and outd.bm=tp1.bm and outd.kfbh=tp1.kfbh
where (bout.ckrq >= @FromTime and bout.ckrq <= @ToTime) and (tp1.bgybh=@czy)
group by outd.lbjdh,outd.bm,outd.kfbh
union all
select outdc.bm,outdc.lbjdh,outdc.kfbh,
sum(outdc.cksl) as outCount,
sum(tp1.jhjg*outdc.cksl) as outPrice
from kf_billoutcundang boutc left join kf_billoutdetailcundang outdc on boutc.ckdh=outdc.ckdh
left join kf_bgyqcsj_view tp1 on outdc.lbjdh=tp1.lbjdh and outdc.bm=tp1.bm and outdc.kfbh=tp1.kfbh
where (boutc.ckrq >= @FromTime and boutc.ckrq <= @ToTime) and (tp1.bgybh=@czy)
group by outdc.lbjdh,outdc.bm,outdc.kfbh) tempbilloutAll
select bm,lbjdh,BGYBH,kfbh,jhjg into #tempwl from kf_bgyqcsj_view where (bgybh=@czy)--#tempbillinout+#tempwlQC+#tempbillOutA +#tempbillinA=#TEMPALL
select wl.kfbh,wl.bm,wl.lbjdh,QCnum=case tempwlQC.kcsl when null then 0
else tempwlQC.kcsl end,wl.jhjg,inCount=case TPI.inCount when null then 0 else TPI.inCount end,
inPrice=case TPI.inPrice when null then 0 else TPI.inPrice end,
outCount=case TPA.outCount when null then 0 else TPA.outCount end,
outPrice=case TPA.outPrice when null then 0 else TPA.outPrice end into #tempALL
from #tempwl wl left join
(select * from kf_wlcundang where (RiQi=@sjcdrq) and (bgybh=@czy)) tempwlQC
on (wl.kfbh =tempwlQC.kfbh and wl.bm=tempwlQC.bm AND wl.lbjdh=tempwlQC.lbjdh)
left join #tempbillinA TPI on wl.kfbh=tpi.kfbh and wl.bm=TPI.bm and wl.LBJDH=TPI.LBJDH
left join
(select kfbh,bm,lbjdh,sum(outCount) as outCount,sum(outPrice) as outPrice
from #tempbilloutAll
group by bm,lbjdh,kfbh) TPA on wl.kfbh=tpa.kfbh and wl.BM=TPA.BM AND wl.LBJDH=TPA.LBJDH select #tempwl.kfbh,kf.kfmc,#tempwl.bm,gys.gysmc,#tempwl.lbjdh,wldy.lbjmc,wldy.ggxh,
wldy.dw,zhxx.shlx,zhxx.sfjcj,#tempALL.QCnum,#tempALL.jhjg,#tempALL.inCount,#tempALL.inPrice,
#tempALL.outCount,#tempALL.outPrice,(#tempALL.QCnum+#tempALL.inCount-#tempALL.outCount)
as StoreAmount,(#tempALL.QCnum+#tempALL.inCount-#tempALL.outCount)*#tempALL.jhjg as StorePrice
from #tempwl left join #tempALL on (#tempwl.kfbh=#tempall.kfbh and #tempwl.bm=#tempall.bm and #tempwl.lbjdh=#tempall.lbjdh)
left join bomwldy wldy on #tempwl.lbjdh=wldy.lbjdh
left join bomgysxx gys on #tempwl.bm=gys.bm left join kf_wlkfzhxx zhxx on #tempwl.lbjdh=zhxx.lbjdh
and #tempwl.kfbh=zhxx.kfbh left join kf_kfxx kf on #tempwl.kfbh=kf.kfbh
order by #tempwl .bm ,#tempwl .lbjdh
drop table #tempwl --drop table #tempbilloutAll
drop table #tempbillinA
drop table #tempALL --set nocount off
end
GO