/* Fun : 取得昨天各个计费代码在各个地区的下行量 Author : YL CreatTime : 2003-12-22 */ CREATE PROCEDURE pro_GetMTNum_Daily @strPrv as varchar(30) = '%' --地区 AS Begin Declare @dateTotalTime as datetime, @dateSTime as datetime, @dateETime as datetime Set @strPrv = ltrim(rtrim(@strPrv)) If @strPrv = '' Set @strPrv = '%' --取得统计日期(当天最后) Set @dateTotalTime = DateAdd(dd, -1, GetDate()) Set @dateTotalTime = Cast(DatePart(yy, @dateTotalTime) as varchar(4)) + '-' + Cast(DatePart(mm, @dateTotalTime) as varchar(2)) + '-' + Cast(DatePart(dd, @dateTotalTime) as varchar(2)) + ' 23:59:59' --取得查询起始,终止日期 Set @dateSTime = Cast(DatePart(yy, @dateTotalTime) as varchar(4)) + '-' + Cast(DatePart(mm, @dateTotalTime) as varchar(2)) + '-' + Cast(DatePart(dd, @dateTotalTime) as varchar(2)) + ' 00:00:00' Set @dateETime = DateAdd(dd, 1, @dateSTime) --数据统计: 统计每个地区,每个计费代码在当天的下行量 Insert Into Msg_Send_Total_Daily_Tbl(Province, area, SvcType, MTNum, TotalTime) Select province, area, SvcType, count(Phone7), @dateTotalTime From Msg_Send_Daily_Tbl as Daily, (Select phone1, phone2, province, area From mobile_area where province like @strPrv) as Area Where SuccTime Between @dateSTime And @dateETime And Phone7 Between phone1 And phone2 Group By province, area, SvcType --统计未在号段范围内的下行量 Insert Into Msg_Send_Total_Daily_Tbl(Province, area, SvcType, MTNum, TotalTime) Select '未知', '未知', SvcType, count(Phone7), @dateTotalTime From Msg_Send_Daily_Tbl as a Where SuccTime Between @dateSTime And @dateETime And Not Exists(Select 1 From Msg_Send_Daily_Tbl as b, (Select phone1, phone2, province, area From mobile_area where province like @strPrv) as Area Where a.Phone7 = b.Phone7 And b.SuccTime Between @dateSTime And @dateETime And b.Phone7 Between phone1 And phone2) Group By SvcType --删除昨天信息 Delete From Msg_Send_Daily_Tbl Where SuccTime Between @dateSTime And @dateETime EndGO这时存储过程中的。将它搬到查询分析器里时只是指定@strPrv = '%'了
你把做下面的替换试试 Cast(DatePart(yy, @dateTotalTime) as varchar(4)) + '-' + Cast(DatePart(mm, @dateTotalTime) as varchar(2)) + '-' + Cast(DatePart(dd, @dateTotalTime) as varchar(2)) 改为下面的: Set @dateTotalTime = convert(char(10),@dateTotalTime,112) + ' 23:59:59'
/*
Fun : 取得昨天各个计费代码在各个地区的下行量
Author : YL
CreatTime : 2003-12-22
*/
CREATE PROCEDURE pro_GetMTNum_Daily
@strPrv as varchar(30) = '%' --地区
AS
Begin
Declare @dateTotalTime as datetime,
@dateSTime as datetime,
@dateETime as datetime Set @strPrv = ltrim(rtrim(@strPrv))
If @strPrv = ''
Set @strPrv = '%' --取得统计日期(当天最后)
Set @dateTotalTime = DateAdd(dd, -1, GetDate())
Set @dateTotalTime = Cast(DatePart(yy, @dateTotalTime) as varchar(4)) + '-' +
Cast(DatePart(mm, @dateTotalTime) as varchar(2)) + '-' +
Cast(DatePart(dd, @dateTotalTime) as varchar(2)) +
' 23:59:59'
--取得查询起始,终止日期
Set @dateSTime = Cast(DatePart(yy, @dateTotalTime) as varchar(4)) + '-' +
Cast(DatePart(mm, @dateTotalTime) as varchar(2)) + '-' +
Cast(DatePart(dd, @dateTotalTime) as varchar(2)) +
' 00:00:00'
Set @dateETime = DateAdd(dd, 1, @dateSTime) --数据统计: 统计每个地区,每个计费代码在当天的下行量
Insert Into Msg_Send_Total_Daily_Tbl(Province, area, SvcType, MTNum, TotalTime)
Select province, area, SvcType, count(Phone7), @dateTotalTime
From Msg_Send_Daily_Tbl as Daily, (Select phone1, phone2, province, area From mobile_area where province like @strPrv) as Area
Where SuccTime Between @dateSTime And @dateETime
And Phone7 Between phone1 And phone2
Group By province, area, SvcType --统计未在号段范围内的下行量
Insert Into Msg_Send_Total_Daily_Tbl(Province, area, SvcType, MTNum, TotalTime)
Select '未知', '未知', SvcType, count(Phone7), @dateTotalTime
From Msg_Send_Daily_Tbl as a
Where SuccTime Between @dateSTime And @dateETime
And Not Exists(Select 1
From Msg_Send_Daily_Tbl as b, (Select phone1, phone2, province, area From mobile_area where province like @strPrv) as Area
Where a.Phone7 = b.Phone7
And b.SuccTime Between @dateSTime And @dateETime
And b.Phone7 Between phone1 And phone2)
Group By SvcType
--删除昨天信息
Delete From Msg_Send_Daily_Tbl
Where SuccTime Between @dateSTime And @dateETime
EndGO这时存储过程中的。将它搬到查询分析器里时只是指定@strPrv = '%'了
Cast(DatePart(yy, @dateTotalTime) as varchar(4)) + '-' +
Cast(DatePart(mm, @dateTotalTime) as varchar(2)) + '-' +
Cast(DatePart(dd, @dateTotalTime) as varchar(2))
改为下面的:
Set @dateTotalTime = convert(char(10),@dateTotalTime,112) +
' 23:59:59'
这个和日期转换无关,真正浪费时间的不是在这里。
改用left join和not exists效果差不多。to zjcxc(邹建)
确实如此神奇,后来试了试,我把“未在号段内的号码统计”这块语句删掉后还是很慢,按说保留统计“号段范围内”的数据这个代码绝对没问题,应该几秒钟就能出来的。
这几个表我都建了必要的索引,但这应该和索引无关的亚