select * from(
(select dwdm,dwmc from ddkhxx where len(dwdm)=4 and dwdm like @dwdm+'%') as ddkhxx ,
(select sum(cast(wlye as numeric(15,2))) from cwkhwlye where jzrq=@qcrq and left(dwdm,4)=ddkhxx.dwdm) qcye,
(select sum(cast(jshj as numeric(15,2))) from ddxhdxx where len(fphm)>4 and fprq>=@begindate and fprq<=@enddate and
left(dwdm,4)=ddkhxx.dwdm) xsje,
(select sum(cast(hlje as numeric(15,2))) from cwhkhlxx where hlrq>=@begindate and hlrq<=@enddate and left(dwdm,4)=ddkhxx.dwdm) hlje,
(select sum(cast(gjs as numeric(15,3))) from ddxhdxx where fprq>=@begindate and fprq<=@enddate and left(dwdm,4)=ddkhxx.dwdm) xszl
)
(select dwdm,dwmc from ddkhxx where len(dwdm)=4 and dwdm like @dwdm+'%') as ddkhxx ,
(select sum(cast(wlye as numeric(15,2))) from cwkhwlye where jzrq=@qcrq and left(dwdm,4)=ddkhxx.dwdm) qcye,
(select sum(cast(jshj as numeric(15,2))) from ddxhdxx where len(fphm)>4 and fprq>=@begindate and fprq<=@enddate and
left(dwdm,4)=ddkhxx.dwdm) xsje,
(select sum(cast(hlje as numeric(15,2))) from cwhkhlxx where hlrq>=@begindate and hlrq<=@enddate and left(dwdm,4)=ddkhxx.dwdm) hlje,
(select sum(cast(gjs as numeric(15,3))) from ddxhdxx where fprq>=@begindate and fprq<=@enddate and left(dwdm,4)=ddkhxx.dwdm) xszl
)
xsje=sum(cast(t3.jshj as numeric(15,2))),
hlje=sum(cast(t4.hlje as numeric(15,2))),
xszl=sum(cast(t1.gjs as numeric(15,3)))
from ddkhxx t1 left join cwkhwlye t2 on t1.dwdm=left(t2.dwdm,4)
left join ddxhdxx t3 on t1.dwdm=left(t3.dwdm,4)
left join cwhkhlxx t4 on t1.dwdm=left(t4.dwdm,4)
where len(t1.dwdm)=4
and t1.dwdm like @dwdm+'%'
and t2.jzrq=@qcrq
and len(t3.fphm)>4
and t1.fprq between(@begindate,@enddate)
and t3.fprq between(@begindate,@enddate)
and t4.hlrq between(@begindate,@enddate)
to: supsuccess(火气不小) 也出错,不过修改后可以执行,但速度更慢了
原来的3-4s,现在要7s
并把dwdm建立索引
还有把from ddkhxx where len(dwdm)=4 and dwdm like @dwdm+'%'
次序换下
from ddkhxx where dwdm like @dwdm+'%' and len(dwdm)=4