各位帮看一下，如何优化一下这两个存储过程？
存储过程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 averagecount else 0 end) as ''21:30'','
set @sql=@sql+'sum(case [hour] when ''14'' then averagecount else 0 end) as ''22:30'','
set @sql=@sql+'sum(case [hour] when ''15'' then averagecount else 0 end) as ''23:30'','
set @sql=@sql+'sum(case [hour] when ''16'' then averagecount else 0 end) as ''00:30'','
set @sql=@sql+'sum(case [hour] when ''17'' then averagecount else 0 end) as ''01:30'','
set @sql=@sql+'sum(case [hour] when ''18'' then averagecount else 0 end) as ''02:30'','
set @sql=@sql+'sum(case [hour] when ''19'' then averagecount else 0 end) as ''03:30'','
set @sql=@sql+'sum(case [hour] when ''20'' then averagecount else 0 end) as ''04:30'','
set @sql=@sql+'sum(case [hour] when ''21'' then averagecount else 0 end) as ''05:30'','
set @sql=@sql+'sum(case [hour] when ''22'' then averagecount else 0 end) as ''06:30'','
set @sql=@sql+'sum(case [hour] when ''23'' then averagecount else 0 end) as ''07:30'','
set @sql=left(@sql,len(@sql)-1)+ 'from #a group by workstage'
select @sql= 'select *,([08:30]+[09:30]+[10:30]+[11:30]+[12:30]+[13:30]+[14:30]+[15:30]
+[16:30]+[17:30]+[18:30]+[19:30]+[20:30]+[21:30]+[22:30]+[23:30]+[00:30]+[01:30]+[02:30]
+[03:30]+[04:30]+[05:30]+[06:30]+[07:30]) as 汇总 from ('+@sql+') as p3'
exec(@sql)
drop table #tmp
drop table #a
GO
不是这样的，比如你的存储过程叫a，那么你新开个查询窗口：
/*订单查询*/
CREATE proc [dbo].[udt_order_workstage_count]
@orderid varchar(12),@startday varchar(10),@endday varchar(10)
as
Set Nocount On
declare @sql varchar(8000)
set @sql = 'select * into #a from (select id as 序号,OrderId as 订单号,'
select @sql = @sql + 'sum(case workstage when '''+workstage+'''
then number else 0 end) as '''+workstage+''','
from (select distinct workstage from v_order_workstage_count) as p0
if @orderid=null and @startday=null
select @sql =left(@sql,len(@sql)-1) + ' from v_order_workstage_count group by OrderId,id) as p1
where convert(varchar(8),left(序号,8),112) between convert(varchar(8),dateadd(d,-4,getdate()),112)
and convert(varchar(8),getdate(),112)'
else
if @orderid!=null and @startday!=null
select @sql =left(@sql,len(@sql)-1) + ' from v_order_workstage_count group by OrderId,id) as p1
where 订单号='''+ @orderid +''' and convert(varchar(8),left(序号,8),112) between
convert(varchar(8),cast('''+@startday+''' as datetime),112) and convert(varchar(8),cast('''+@endday+''' as datetime) ,112)'
else
if @orderid=null and @startday!=null
select @sql =left(@sql,len(@sql)-1) + ' from v_order_workstage_count group by OrderId,id) as p1
where convert(varchar(8),left(序号,8),112) between convert(varchar(8),cast('''+@startday+''' as datetime),112)
and convert(varchar(8),cast('''+@endday+''' as datetime),112)'
else
if @orderid!=null and @startday=null
select @sql =left(@sql,len(@sql)-1) + ' from v_order_workstage_count group by OrderId,id) as p1
where 订单号='''+ @orderid +''''
select @sql=@sql+'select distinct #a.*,engType as 机型
from #a left JOIN dbo.tbl_planOrder ON dbo.tbl_planOrder.orderid = #a.订单号
union all
select null,''汇总'',sum([ 订单数量]),sum([1030]),sum([1450]),sum([3010]),sum([3310]),sum([4300]),sum([6140]),null from #a
order by 序号 desc
drop table #a'
exec(@sql)
GO
