分别按照日期：时：天：月：年进行统计信息代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】分别按照日期：时：天：月：年进行统计信息

作者:Dezai.CN / 发布于2011/7/26/ 567

<div>
CREATE PROC [dbo].tongjixinxi
@KeywordId int,
@Type int=0 --0:日-4,8,12,16,20,24
--1:周-1,2,3,4,5,6,7
--2:月-5,10,15,20,25,di
--3:年-3,,6,9,12
<a href="mailto:--@TotalInfo"><font color="#075db3">--@TotalInfo</font></a> int output
AS
BEGIN
DECLARE @PageTemp1 table 
(
Pid Int,
KeywordId INT,
Title varchar(255), 
Brief varchar(255), 
Link varchar(255), 
CreatedDate datetime,
LastUpdatedDate datetime 
) 
DECLARE @PageTemp2 table 
(
Pid Int,
KeywordId INT,
Title varchar(255), 
Brief varchar(255), 
Link varchar(255), 
CreatedDate datetime,
LastUpdatedDate datetime 
) 
INSERT INTO @PageTemp1 SELECT P.Id,T.KeywordId,T.Title,P.Brief,P.Link,P.CreatedDate,P.LastUpdatedDate FROM PageSnapTitles T inner JOIN PageSnaps P ON T.Id=P.TitleId where <a href="mailto:T.KeywordId=@KeywordId"><font color="#075db3">T.KeywordId=@KeywordId</font></a>
IF(@Type=0) --0:日-4,8,12,16,20,24----NOTE:此处为测试使用；等项目完成；正式使用该存储过程可删去；
BEGIN
DECLARE @num INT 
SELECT @num=COUNT(*) FROM PageSnaps WHERE DAY(LastUpdatedDate) =DAY(GETDATE())-1
IF(@num>3)
BEGIN
SELECT DATEPART(HOUR,T.LastUpdatedDate) AS 'Date',COUNT(T.Pid) AS PsCount FROM 
(SELECT * FROM @PageTemp1 WHERE DATEPART(HOUR,LastUpdatedDate) 
BETWEEN DATEPART(HOUR,GETDATE()) AND DATEPART(HOUR,GETDATE())-24) AS T 
GROUP BY DATEPART(HOUR,LastUpdatedDate) 
ORDER BY DATEPART(HOUR,LastUpdatedDate) asc
END
ELSE
BEGIN
SELECT DATEPART(HOUR,LastUpdatedDate) AS 'Date',COUNT(*) AS PsCount FROM @PageTemp1 GROUP BY DATEPART(HOUR,LastUpdatedDate) ORDER BY DATEPART(HOUR,LastUpdatedDate) asc
END 
END
ELSE IF(@Type=1)-----1:周-1,2,3,4,5,6,7
BEGIN
select datename(weekday,T.LastUpdatedDate)AS 'Date',COUNT(T.Pid) AS PsCount
from (SELECT * FROM @PageTemp1 WHERE DATEPART(WEEK,LastUpdatedDate) 
BETWEEN DATEPART(WEEK,GETDATE()) AND DATEPART(WEEK,GETDATE())+1) AS T ----为了验证数据有待修改
GROUP BY DATENAME(weekday,T.LastUpdatedDate) ORDER BY datename(weekday,T.LastUpdatedDate)
ASC
END
ELSE IF(@Type=2)--2:月-5,10,15,20,25,di
BEGIN
SELECT convert(varchar(10),LastUpdatedDate,120) as 'Date', count(Pid) as PsCount from @PageTemp1
group by convert(varchar(10),LastUpdatedDate,120) 
order By convert(varchar(10),LastUpdatedDate,120) ASC
END
ELSE IF(@Type=3)--3:年-3,,6,9,12
BEGIN
SELECT DATENAME(YEAR,T.LastUpdatedDate) AS 'Date',COUNT(T.Pid) AS PsCount FROM 
(SELECT *FROM @PageTemp1 WHERE YEAR(LastUpdatedDate) 
BETWEEN YEAR(DATEADD(YEAR,-10,getdate())) AND YEAR(getdate()))AS T GROUP BY 
DATENAME(YEAR,T.LastUpdatedDate) ORDER BY DATENAME(YEAR,T.LastUpdatedDate) ASC 
END
END
</div>

试试其它关键字

　统计信息　

同语言下

可能有用的

Dezai.CN贡献的其它代码(4037)