这条语句还能优化吗?

[jackluo1981]

declare @S1 datetime
declare @S2 datetime
select @S1='2005-08-01'
select @S2='2005-08-31'
select a.modelNo as ModelNo,count(1) as num,
avg(datediff(mi,RecDate,GmtDate)) as InStoreTime, --在库时间
avg(datediff(mi,GmtDate,a.checkdate)) as WaitProduce, --生产滞留时间
avg(datediff(mi,a.checkdate,b.checkdate)) as WaitTime, --在制造滞留时间
avg(datediff(mi,b.checkdate,c.checkdate)) as ProductTime, --制造时间
avg(datediff(mi,c.checkdate,d.checkdate)) as OQCTime, --OQC时间
avg(datediff(mi,d.checkdate,e.checkdate)) as PackTime, --包装时间
avg(datediff(mi,b.checkdate,e.checkdate)) as SumProductTime, --生产时间
avg(datediff(mi,a.checkdate,e.checkdate)) as TotleTime --总时间
from ( select Producth.modelNo,Productd1.Productid,Productd1.checkdate from Productd1,producth where Producth.Productid=Productd1.Productid and
Productd1.ProductProject='1000' and PackDate>=@S1 and PackDate<=@S2) a,
(select Productd1.Productid,Productd1.checkdate from Productd1,producth where Producth.Productid=Productd1.Productid and
Productd1.ProductProject='1010' and PackDate>=@S1 and PackDate<=@S2) b,
(select Productd1.Productid,Productd1.checkdate from Productd1,producth where Producth.Productid=Productd1.Productid and
(Productd1.ProductProject='1700' or Productd1.ProductProject='1450' or Productd1.ProductProject='1521')
and PackDate>=@S1 and PackDate<=@S2) c,
(select Productd1.Productid,Productd1.checkdate from Productd1,producth where Producth.Productid=Productd1.Productid and
Productd1.ProductProject='1500' and PackDate>=@S1 and PackDate<=@S2) d,
(select Productd1.Productid,Productd1.checkdate from Productd1,producth where Producth.Productid=Productd1.Productid and
Productd1.ProductProject='1501' and PackDate>=@S1 and PackDate<=@S2) e,
(select Producth.Productid ,RecDate from Producth,Productd1 where Producth.Productid=Productd1.Productid and PackDate>=@S1 and PackDate<=@S2 ) f,
(select Producth.Productid ,GmtDate from Producth,Productd1 where Producth.Productid=Productd1.Productid and PackDate>=@S1 and PackDate<=@S2 ) g
where a.Productid = b.Productid and c.Productid = b.Productid and d.Productid = b.Productid
and e.Productid = b.Productid and f.Productid = b.Productid and g.Productid = b.Productid group by a.modelNo order by a.ModelNo
运行时间太长,请问各位如何优化!

[zkw]

帮你顶

[iwl]

declare @S1 datetime
declare @S2 datetime
select @S1='2005-08-01'
select @S2='2005-08-31'
select Producth.modelNo as ModelNo,count(1) as num,
avg(datediff(mi,RecDate,GmtDate)) as InStoreTime, --在库时间
avg(datediff(mi,GmtDate,a.checkdate)) as WaitProduce, --生产滞留时间
avg(datediff(mi,a.checkdate,b.checkdate)) as WaitTime, --在制造滞留时间
avg(datediff(mi,b.checkdate,c.checkdate)) as ProductTime, --制造时间
avg(datediff(mi,c.checkdate,d.checkdate)) as OQCTime, --OQC时间
avg(datediff(mi,d.checkdate,e.checkdate)) as PackTime, --包装时间
avg(datediff(mi,b.checkdate,e.checkdate)) as SumProductTime, --生产时间
avg(datediff(mi,a.checkdate,e.checkdate)) as TotleTime --总时间
from Productd1,producth
where Productd1.ProductProject IN ('1000','1010','1700','1450','1521','1500','1501') and Producth.Productid=Productd1.Productid and
PackDate>=@S1 and PackDate<=@S2
group by Producth.modelNo order by Producth.modelNo

[longwulaoda]

臨時表

[gimy007]

一些镶套的SQL可以用临时表，这可以提高效率

[vbasten]

关注

[lula]

关注