各位帮看一上，怎么优化一上这两个存储过程
日期：2014-05-17 浏览次数：20438 次
各位帮看一下，如何优化一下这两个存储过程？
存储过程1：
存储过程1：
/*计算小时平均产量*/
CREATE PROCEDURE [dbo].[udt_avghourcount]
@startdate datetime,@enddate datetime
AS
Set Nocount On
/*@days 两日期之间有产量的天数*/
declare @days int
select @days=count([date]) from(
select DISTINCT convert(varchar(10),recordtime,23) as [date]
from tbl_definetimerecord
where recordtime between @startdate and dateadd(d,1,@enddate)) as p0
select * into #tmp from(select workstage,convert(varchar(13),recordtime,20) as [time],
count(engid)*1.0 as total
from tbl_definetimerecord
where recordtime between @startdate and dateadd(d,1,@enddate)
group by convert(varchar(13),recordtime,20) ,workstage) as p1
select * into #a from (select top 100 percent workstage,right(time,2) as [hour],cast(sum(total)*1.0/(@days*1.0)
as decimal(5,2)) as averagecount
from #tmp
group by workstage,right(time,2)
order by workstage) as p2
declare @sql varchar(8000)
set @sql='select case workstage
when ''1030'' then ''内装上线''
when ''1450'' then ''内装下线''
when ''3010'' then ''外装上线''
when ''3310'' then ''外装下线''
when ''4300'' then ''预装下线''
when ''6040'' then ''装风扇''
when ''6140'' then ''包装''
else workstage end as 工序,'
set @sql=@sql+'sum(case [hour] when ''00'' then averagecount else 0 end) as ''08:30'','
set @sql=@sql+'sum(case [hour] when ''01'' then averagecount else 0 end) as ''09:30'','
set @sql=@sql+'sum(case [hour] when ''02'' then averagecount else 0 end) as ''10:30'','
set @sql=@sql+'sum(case [hour] when ''03'' then averagecount else 0 end) as ''11:30'','
set @sql=@sql+'sum(case [hour] when ''04'' then averagecount else 0 end) as ''12:30'','
set @sql=@sql+'sum(case [hour] when ''05'' then averagecount else 0 end) as ''13:30'','
set @sql=@sql+'sum(case [hour] when ''06'' then averagecount else 0 end) as ''14:30'','
set @sql=@sql+'sum(case [hour] when ''07'' then averagecount else 0 end) as ''15:30'','
set @sql=@sql+'sum(case [hour] when ''08'' then averagecount else 0 end) as ''16:30'','
set @sql=@sql+'sum(case [hour] when ''09'' then averagecount else 0 end) as ''17:30'','
set @sql=@sql+'sum(case [hour] when ''10'' then averagecount else 0 end) as ''18:30'','
set @sql=@sql+'sum(case [hour] when ''11'' then averagecount else 0 end) as ''19:30'','
set @sql=@sql+'sum(case [hour] when ''12'' then averagecount else 0 end) as ''20:30'','
set @sql=@sql+'sum(case [hour] when ''13'' then
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
