应该这样做。递归CTE用于构建运行时间列表:
;WITH rangeCTE
AS
(
SELECT MIN(DATE) minDate
,MAX(DATE) maxDate
FROM #temp
)
,timeListCTE
AS
(
SELECT CAST(CONVERT(CHAR(14),minDate,121) + '00:00' AS DATETIME) AS timeStart
,DATEADD(hh,1,CAST(CONVERT(CHAR(14),minDate,121) + '00:00' AS DATETIME)) AS timeEnd
,1 AS timeID
FROM rangeCTE
UNION ALL
SELECT DATEADD(hh,1,timeStart)
,DATEADD(hh,2,timeStart)
,timeID + 1
FROM timeListCTE
WHERE timeStart <= (SELECT maxDate FROM rangeCTE)
)
SELECT tl.timeStart
,tl.timeEnd
,SUM(CASE WHEN t.Date IS NOT NULL
THEN 1
ELSE 0
END
)
FROM timeListCTE AS tl
LEFT JOIN #temp AS t
ON t.DATE >= tl.timeStart
AND t.DATE < tl.timeEnd
GROUP BY tl.timeStart
,tl.timeEnd
