请帮忙优化一下SQL

Question

建议使用临时表和while循环来取代游标

Answer 1

我已经改成这样了,还是很慢,请高人指点啊:
create  procedure datagsreport @wtype varchar,@year int
as 
set nocount on select ac.* into #tb from ( 
SELECT c.[group] AS custgroup, c.customer,e.pfamily,d.pname, a.productno,year(a.fabdate) as okmyear,
case  month(a.fabdate) 
when 1 then 1
when 2 then 1
when 3 then 1
when 4 then 2
when 5 then 2
when 6 then 2
when 7 then 3
when 8 then 3
when 9 then 3
when 10 then 4
when 11 then 4
when 12 then 4
end 
as okmqur, fac.factoryname ,
case when  left(a.analysisresult,1)='4' then a.number 
when  left(a.analysisresult,1)='5' then b.number else 0 end as bnum,
case when  left(a.analysisresult,1)='2' then a.number else 0 end as cnum,
case when  left(a.analysisresult,1)='3' then a.number else 0 end as snum,
  a.number as pcs ,a.implemdate ,a.dept,a.improvement ,a.u8dreport ,
case when  left(a.analysisresult,1)='4' then rs.causedescen 
  when  left(a.analysisresult,1)='5' then rs.causedescen  else null  end as  uaescause 
FROM dbo.complaintinfob a INNER JOIN
      dbo.complaintinfoa b ON b.caID = a.caID AND a.type  <>  'CP'   AND b.status = 'E'  
     and a.typeno in (select typeno from reportswt where rid=25) 
      and year(a.fabdate) between @year-2  and @year   INNER JOIN
      dbo.customerinfo c ON a.customer = c.customer AND 
      c.customer NOT LIKE 'Retail%' INNER JOIN
      dbo.productno d ON a.productno = d.productno
      inner join productinfo e on b.pname=e.pname INNER JOIN failurecode  f on a.analysisresult=f.failurecode
 INNER JOIN ii_factory fac on e.ftid=fac.ftid inner join rootcause rs on a.rootcause=rs.id ) ac 
select custgroup,customer,pfamily,pname,'' as productno ,okmyear,okmqur,factoryname,
case when isnull(sum(bnum),0) = 0 then null else 
(    select top 1 uaescause  
 from #tb bb
      where   isnull(ac.custgroup,' ')=isnull(bb.custgroup,' ')  
      and isnull(ac.customer,' ') =isnull(bb.customer,' ')  
      and isnull(ac.pfamily,' ') =isnull(bb.pfamily,' ')  
      and  isnull(ac.pname,' ') =isnull(bb.pname,' ')  
      and ac.okmyear    =bb.okmyear and ac.okmqur =bb.okmqur
      and isnull(ac.factoryname,' ') =isnull(bb.factoryname,' '  ) 
      group by uaescause ,improvement,implemdate,dept, u8dreport  
      order by count(uaescause) desc 
 )  end as uaescause  ,
 case when isnull(sum(bnum),0) = 0 then null else 
 (    select top 1 dept  
 from #tb bb
      where   isnull(ac.custgroup,' ')=isnull(bb.custgroup,' ')  
      and isnull(ac.customer,' ') =isnull(bb.customer,' ')  
      and isnull(ac.pfamily,' ') =isnull(bb.pfamily,' ')  
      and  isnull(ac.pname,' ') =isnull(bb.pname,' ')  
      and ac.okmyear    =bb.okmyear and ac.okmqur =bb.okmqur
      and isnull(ac.factoryname,' ') =isnull(bb.factoryname,' '  ) 
      group by uaescause ,improvement,implemdate,dept, u8dreport  
      order by count(uaescause) desc 
 )  end as dept ,
 case when isnull(sum(bnum),0) = 0 then null else 
 (    select top 1 improvement  
 from #tb bb
      where   isnull(ac.custgroup,' ')=isnull(bb.custgroup,' ')  
      and isnull(ac.customer,' ') =isnull(bb.customer,' ')  
      and isnull(ac.pfamily,' ') =isnull(bb.pfamily,' ')  
      and  isnull(ac.pname,' ') =isnull(bb.pname,' ')  
      and ac.okmyear    =bb.okmyear and ac.okmqur =bb.okmqur
      and isnull(ac.factoryname,' ') =isnull(bb.factoryname,' '  ) 
      group by uaescause ,improvement,implemdate,dept, u8dreport  
      order by count(uaescause) desc 
 )  end as  improvement,
 case when isnull(sum(bnum),0) = 0 then null else  
 (    select top 1 implemdate  
 from #tb bb
      where   isnull(ac.custgroup,' ')=isnull(bb.custgroup,' ')  
      and isnull(ac.customer,' ') =isnull(bb.customer,' ')  
      and isnull(ac.pfamily,' ') =isnull(bb.pfamily,' ')  
      and  isnull(ac.pname,' ') =isnull(bb.pname,' ')  
      and ac.okmyear    =bb.okmyear and ac.okmqur =bb.okmqur
      and isnull(ac.factoryname,' ') =isnull(bb.factoryname,' '  ) 
      group by uaescause ,improvement,implemdate,dept, u8dreport  
      order by count(uaescause) desc 
 )  end as implemdate ,
 case when isnull(sum(bnum),0) = 0 then null else 
  (    select top 1 u8dreport  
 from #tb bb
      where   isnull(ac.custgroup,' ')=isnull(bb.custgroup,' ')  
      and isnull(ac.customer,' ') =isnull(bb.customer,' ')  
      and isnull(ac.pfamily,' ') =isnull(bb.pfamily,' ')  
      and  isnull(ac.pname,' ') =isnull(bb.pname,' ')  
      and ac.okmyear    =bb.okmyear and ac.okmqur =bb.okmqur
      and isnull(ac.factoryname,' ') =isnull(bb.factoryname,' '  ) 
      group by uaescause ,improvement,implemdate,dept, u8dreport  
      order by count(uaescause) desc 
 )  end as u8dreport,
isnull(sum(pcs),0) as pcs, isnull(sum(bnum),0) as bnum,
isnull(sum(cnum),0) as cnum,
isnull(sum(snum),0) as snum,
isnull(sum(qty),0) as qty 
 from (  select
custgroup,customer,pfamily,pname,okmyear,okmqur,productno,factoryname,sum(pcs) as pcs, sum(bnum) as bnum,
sum(cnum) as cnum,
sum(snum) as snum ,case okmqur when 1 then (select sum(quantity)  from sales   where
productno=ab.productno
and year(pdate)=ab.okmyear and month(pdate)  between 1 and 3)
when 2 then (select sum(quantity)  from sales where
productno=ab.productno
and year(pdate)=ab.okmyear and month(pdate) between 4 and 6)
when 3 then (select  sum(quantity)  from sales  where
productno=ab.productno
and year(pdate)=ab.okmyear and month(pdate) between 7 and 9)
when 4 then  (select sum(quantity)  from sales where
productno=ab.productno
and year(pdate)=ab.okmyear and month(pdate) between 10 and 12)
end  as qty  from  #tb ab  group by custgroup,customer,pfamily,pname,productno,okmyear,okmqur,factoryname ) ac
 group by custgroup,customer,pfamily,pname,okmyear,okmqur,factoryname 
order by custgroup,customer,pfamily,pname,okmyear,okmqur,factoryname 
drop table #tb