我重新写了存储过程速度很快了已经!但是上述问题,仍然是个迷 重写存储过程如下-----start----------set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go -- ============================================= -- Author: tiny c -- Create date: 2009-06-24 -- Description: 基站话务量 -- exec P_Get_TCH_MEAN_Site @Type='日汇总',@StartDate='2009-06-22',@EndDate='2009-06-23',@CountyID='421122' -- =============================================alter PROCEDURE [dbo].[P_Get_TCH_MEAN_Site] @Type varchar(20)=NULL,----类别:‘日汇总’、‘月汇总’ @StartDate datetime=NULL, @EndDate datetime=NULL, @CountyID varchar(6)=NULL---县市ID AS if @Type is null set @Type='' if @CountyID is null set @CountyID='' if @Type='' return if @Type='日汇总' begin if (@StartDate is null) or (@StartDate='') set @StartDate=cast(convert(varchar(10),dateadd(day,-1,Getdate()),120)+' 00:00:00' as datetime) if (@EndDate is null) or (@EndDate='') set @EndDate=cast(convert(varchar(10),dateadd(day,-1,Getdate()),120)+' 23:59:59' as datetime) if (@CountyID='') begin select a.*,b.CountyName as 县市 from ( select 统计时间,SiteNum as 基站编号,SiteName as 基站名称,round(isnull(TCH_MEAN,0),2) as 全天话务量,round(isnull(BUSY_TCH_MEAN,0),2) as 日忙时话务量 from [QYWZC_StasticData].dbo.TCHMEAN_Day_Site where 统计时间>=@StartDate and 统计时间<=@EndDate ) as a, V_CurrentSite as b where a.基站名称=b.SiteName order by 基站编号,统计时间 asc end else begin select a.* from( select a.*,b.CountyName as 县市 from ( select 统计时间,SiteNum as 基站编号,SiteName as 基站名称,round(isnull(TCH_MEAN,0),2) as 全天话务量,round(isnull(BUSY_TCH_MEAN,0),2) as 日忙时话务量 from [QYWZC_StasticData].dbo.TCHMEAN_Day_Site where 统计时间>=@StartDate and 统计时间<=@EndDate ) as a, V_CurrentSite as b where a.基站名称=b.SiteName ) as a,dbo.T_D_County as b where a.县市=b.CountyName and b.CountyID=@CountyID order by 基站编号,统计时间 asc end end else if @Type='月汇总' begin if (@StartDate is null) or (@StartDate='') set @StartDate=cast(convert(varchar(7),dateadd(month,0,Getdate()),120)+'-01 00:00:00' as datetime) if (@EndDate is null) or (@EndDate='') set @EndDate=cast(convert(varchar(7),dateadd(month,0,Getdate()),120)+'-01 00:00:00' as datetime) set @EndDate=dateadd(month,1,@EndDate) if (@CountyID='') begin select a.*,b.CountyName as 县市 from ( select convert(varchar(7),统计时间,120) as 统计时间,max(SiteNum) as 基站编号,Max(SiteName) as 基站名称,round(SUM(isnull(TCH_MEAN,0)),2) as 全天话务量,round(sum(isnull(BUSY_TCH_MEAN,0)),2) as 日忙时话务量 from [QYWZC_StasticData].dbo.TCHMEAN_Day_Site where 统计时间>=@StartDate and 统计时间<@EndDate group by convert(varchar(7),统计时间,120),SiteNum ) as a, V_CurrentSite as b where a.基站名称=b.SiteName order by 基站编号,统计时间 asc end else begin select a.* from( select a.*,b.CountyName as 县市 from ( select convert(varchar(7),统计时间,120) as 统计时间,max(SiteNum) as 基站编号,Max(SiteName) as 基站名称,round(SUM(isnull(TCH_MEAN,0)),2) as 全天话务量,round(sum(isnull(BUSY_TCH_MEAN,0)),2) as 日忙时话务量 from [QYWZC_StasticData].dbo.TCHMEAN_Day_Site where 统计时间>=@StartDate and 统计时间<@EndDate group by convert(varchar(7),统计时间,120),SiteNum ) as a, V_CurrentSite as b where a.基站名称=b.SiteName ) as a,dbo.T_D_County as b where a.县市=b.CountyName and b.CountyID=@CountyID order by 基站编号,统计时间 asc end end-----end----------
重写存储过程如下-----start----------set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: tiny c
-- Create date: 2009-06-24
-- Description: 基站话务量
-- exec P_Get_TCH_MEAN_Site @Type='日汇总',@StartDate='2009-06-22',@EndDate='2009-06-23',@CountyID='421122'
-- =============================================alter PROCEDURE [dbo].[P_Get_TCH_MEAN_Site]
@Type varchar(20)=NULL,----类别:‘日汇总’、‘月汇总’
@StartDate datetime=NULL,
@EndDate datetime=NULL,
@CountyID varchar(6)=NULL---县市ID
AS
if @Type is null set @Type=''
if @CountyID is null set @CountyID=''
if @Type='' return
if @Type='日汇总'
begin
if (@StartDate is null) or (@StartDate='') set @StartDate=cast(convert(varchar(10),dateadd(day,-1,Getdate()),120)+' 00:00:00' as datetime)
if (@EndDate is null) or (@EndDate='') set @EndDate=cast(convert(varchar(10),dateadd(day,-1,Getdate()),120)+' 23:59:59' as datetime)
if (@CountyID='')
begin
select a.*,b.CountyName as 县市 from (
select 统计时间,SiteNum as 基站编号,SiteName as 基站名称,round(isnull(TCH_MEAN,0),2) as 全天话务量,round(isnull(BUSY_TCH_MEAN,0),2) as 日忙时话务量
from [QYWZC_StasticData].dbo.TCHMEAN_Day_Site
where 统计时间>=@StartDate and 统计时间<=@EndDate
) as a, V_CurrentSite as b
where a.基站名称=b.SiteName
order by 基站编号,统计时间 asc
end
else
begin
select a.* from(
select a.*,b.CountyName as 县市 from (
select 统计时间,SiteNum as 基站编号,SiteName as 基站名称,round(isnull(TCH_MEAN,0),2) as 全天话务量,round(isnull(BUSY_TCH_MEAN,0),2) as 日忙时话务量
from [QYWZC_StasticData].dbo.TCHMEAN_Day_Site
where 统计时间>=@StartDate and 统计时间<=@EndDate
) as a, V_CurrentSite as b
where a.基站名称=b.SiteName
) as a,dbo.T_D_County as b where a.县市=b.CountyName and b.CountyID=@CountyID
order by 基站编号,统计时间 asc
end
end else if @Type='月汇总'
begin
if (@StartDate is null) or (@StartDate='') set @StartDate=cast(convert(varchar(7),dateadd(month,0,Getdate()),120)+'-01 00:00:00' as datetime)
if (@EndDate is null) or (@EndDate='') set @EndDate=cast(convert(varchar(7),dateadd(month,0,Getdate()),120)+'-01 00:00:00' as datetime) set @EndDate=dateadd(month,1,@EndDate)
if (@CountyID='')
begin
select a.*,b.CountyName as 县市 from (
select convert(varchar(7),统计时间,120) as 统计时间,max(SiteNum) as 基站编号,Max(SiteName) as 基站名称,round(SUM(isnull(TCH_MEAN,0)),2) as 全天话务量,round(sum(isnull(BUSY_TCH_MEAN,0)),2) as 日忙时话务量
from [QYWZC_StasticData].dbo.TCHMEAN_Day_Site
where 统计时间>=@StartDate and 统计时间<@EndDate
group by convert(varchar(7),统计时间,120),SiteNum
) as a, V_CurrentSite as b
where a.基站名称=b.SiteName
order by 基站编号,统计时间 asc
end
else
begin
select a.* from(
select a.*,b.CountyName as 县市 from (
select convert(varchar(7),统计时间,120) as 统计时间,max(SiteNum) as 基站编号,Max(SiteName) as 基站名称,round(SUM(isnull(TCH_MEAN,0)),2) as 全天话务量,round(sum(isnull(BUSY_TCH_MEAN,0)),2) as 日忙时话务量
from [QYWZC_StasticData].dbo.TCHMEAN_Day_Site
where 统计时间>=@StartDate and 统计时间<@EndDate
group by convert(varchar(7),统计时间,120),SiteNum
) as a, V_CurrentSite as b
where a.基站名称=b.SiteName
) as a,dbo.T_D_County as b where a.县市=b.CountyName and b.CountyID=@CountyID
order by 基站编号,统计时间 asc
end
end-----end----------