计算特定时间范围之间的访问次数

;WITH CTE AS (

SELECT 
substring(convert(varchar(20), GeneratedAt, 9), 13, 5) + ' ' + 
substring(convert(varchar(30), GeneratedAt, 9), 25, 2) AS GetTime
FROM #Temp

)

在这里，我转换为时间值作为AM/PM

SELECT * FROM
( 
SELECT '7 AM' AS TimeFrame,SUM(CASE WHEN GetTime BETWEEN Convert(datetime,'7 AM') AND Convert(datetime,'10 AM') THEN 1 ELSE 0 END) AS COUNT FROM CTE
UNION 
SELECT '10 AM' AS TimeFrame,SUM(CASE WHEN GetTime BETWEEN Convert(datetime,'10 AM') AND Convert(datetime,'1 PM') THEN 1 ELSE 0 END) AS COUNT FROM CTE 
UNION 
SELECT '1 PM' AS TimeFrame,SUM(CASE WHEN GetTime BETWEEN Convert(datetime,'1 PM') AND Convert(datetime,'4 PM') THEN 1 ELSE 0 END) AS COUNT FROM CTE 
UNION 
SELECT '4 PM' AS TimeFrame,SUM(CASE WHEN GetTime BETWEEN Convert(datetime,'4 PM') AND Convert(datetime,'7 PM') THEN 1 ELSE 0 END) AS COUNT FROM CTE
UNION 
SELECT '7 PM' AS TimeFrame,SUM(CASE WHEN GetTime BETWEEN Convert(datetime,'7 PM') AND Convert(datetime,'10 PM') THEN 1 ELSE 0 END) AS COUNT FROM CTE
UNION 
SELECT '10 PM' AS TimeFrame,SUM(CASE WHEN GetTime BETWEEN Convert(datetime,'10 PM') AND Convert(datetime,'1 AM') THEN 1 ELSE 0 END) AS COUNT FROM CTE
)a 
ORDER BY Convert(datetime,a.TimeFrame)

而不是硬代码值使用可以使用@Parameter或其他东西...根据您的喜好