谁帮我优化一下这个SQL语句？在线等
--废品率图表的废品率查询
declare @dtCur datetime
declare @Faculty varchar(4)
declare @dtB datetime
declare @dtE datetime
declare @dtTemp datetime
set @dtCur = '2007-1-1 '
set @Faculty= '三科 '
set @dtB= '2007-1-1 '
set @dtE= '2007-1-31 '
set @dtB = convert(datetime,convert(char(8),@dtCur,120)+ '1 ')
set @dtE = Dateadd(day,-1,convert(char(8),dateadd(month,1,@dtCur),120)+ '1 ')
set @dtTemp=@dtB
create table dt(d varchar(8),dt datetime)
declare @MonthDay varchar(8)
while(@dtTemp <=@dtE)
begin
set @MonthDay = cast(month(@dtTemp) as varchar(2))+ '- '+cast(day(@dtTemp) as varchar(2))
insert into dt (d,dt)
select @MonthDay,@dtTemp
set @dtTemp = dateadd(day,1,@dtTemp)
end
---114筛选 103修剪（我就觉地下面的语句效率很低，查询很慢，应该优化）
select d,T.dt,a,w,Ajc,Rate from dt T left join
(select dt,sum(a) a,sum(w) w,sum(AJc) AJc,
Case when sum(AJc) > 0 then cast((1.00*sum(W)/sum(AJc)*100) as numeric(10,2)) else 0 end as Rate
from
(select convert(char(10),a.produceDate,120) dt,a,w,
case when ASx is not NULL
then ASx else AXj end as Ajc
from (select sum(WasterQtyT) w,ProductNo,ProduceDate
from gwkBaseData
where ProduceDate> =@dtB
and ProduceDate <=@dtE
and Faculty=@Faculty
group by ProductNo,ProduceDate) A left join (
select sum(ActualQty+WasterQtyT) A,ProductNo,ProduceDate
from gwkBaseData
where ProduceDate> =@dtB
and ProduceDate <=@dtE and WPNo= '101 '
and Faculty=@Faculty
group by ProductNo,ProduceDate) B on A.ProductNo = B.ProductNo and A.ProduceDate = B.ProduceDate left join
(select sum(ActualQty+WasterQtyT) ASx,ProductNo,ProduceDate
from gwkBaseData
where ProduceDate> =@dtB
and ProduceDate <=@dtE and WPNo= '114 '
and Faculty=@Faculty
group by ProductNo,ProduceDate) C on A.ProductNo = C.ProductNo and A.ProduceDate = C.ProduceDate left join
(select sum(ActualQty+WasterQtyT) AXj,ProductNo,ProduceDate
from gwkBaseData
where ProduceDate> =@dtB
and ProduceDate <=@dtE and WPNo= '103 '
and Faculty=@Faculty
group by ProductNo,ProduceDate) D on A.ProductNo = d.ProductNo and A.ProduceDate = D.ProduceDate) G group by dt) H on T.dt=H.dt
drop table dt
GO
------解决方案--------------------
--废品率图表的废品率查询
declare @dtCur datetime
declare @Faculty varchar(4)
declare @dtB datetime
declare @dtE datetime
declare @dtTemp datetime
set @dtCur = '2007-1-1 '
set @Faculty= '三科 '
set @dtB= '2007-1-1 '
set @dtE= '2007-1-31 '
set @dtB = convert(datetime,convert(char(8),@dtCur,120)+ '1 ')
set @dtE = Dateadd(day,-1,convert(char(8),dateadd(month,1,@dtCur),120)+ '1 ')
set @dtTemp=@dtB
create table dt(d varchar(8),dt datetime)
declare @MonthDay varchar(8)
while(@dtTemp <=@dtE)
begin
set @MonthDay = cast(month(@dtTemp) as varchar(2))+ '- '+cast(day(@dtTemp) as varchar(2))
insert into dt (d,dt)
select @MonthDay,@dtTemp
set @dtTemp = dateadd(day,1,@dtTemp)
end--select * from dt --drop table dt
---114筛选 103修剪（我就觉地下面的语句效率很低，查询很慢，应该优化）
select d,T.dt,a,w,Ajc,Rate from dt T left join
(select dt,sum(a) a,sum(w) w,sum(AJc) AJc,
Case when sum(AJc) > 0 then cast((1.00*sum(W)/sum(AJc)*100) as numeric(10,2)) else 0 end as Rate
from(
select convert(char(10),a.produceDate,120) dt,
sum(case WPNo when '101 ' then ActualQty+WasterQtyT else 0 end) A,
sum(WasterQtyT) W,
sum(case WPNo when '114 ' then ActualQty+WasterQtyT when '103 ' then ActualQty+WasterQtyT end) Ajc
from gwkBaseData
where ProduceDate> =@dtB and ProduceDate <=@dtE and Faculty=@Faculty
group by ProductNo,ProduceDate
)
G group by dt) H on T.dt=H.dt
drop table dt
GO
