我试图每小时从大型SQL DB中提取数据。以下查询为我提供了按分钟分组的信息。我希望按小时对数据进行分组。
select CONVERT(VARCHAR(20), t.counterTime, 100) as [Time],
t.instanceName
from table as t
WHERE t.counterId= @counterId
and t.counterTime >= @startTime
and t.counterTime < @endTime
group by t.instanceName, CONVERT(VARCHAR(20), t.counterTime, 100)
该文档也未提供任何此类选项。有任何想法吗?
这样尝试
--SQL Server 2012
SELECT FORMAT(t.counterTime, 'yyyy-MM-dd HH') AS [Time]
,t.instanceName
FROM TABLE AS t
WHERE t.counterId = @counterId
AND t.counterTime >= @startTime
AND t.counterTime < @endTime
GROUP BY t.instanceName
,FORMAT(t.counterTime, 'yyyy-MM-dd HH')
--for earlier versions
SELECT CONVERT(CHAR(13), t.counterTime, 120) AS [Time]
,t.instanceName
FROM TABLE AS t
WHERE t.counterId = @counterId
AND t.counterTime >= @startTime
AND t.counterTime < @endTime
GROUP BY t.instanceName
,CONVERT(CHAR(13), t.counterTime, 120)
Note: Format would be perfoming slower.
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句