就像我做的那样
SELECT [Date]
FROM [FRIIB].[dbo].[ArchiveAnalog]
GROUP BY [Date]
如何指定分组时段?我使用的是MS SQL 2008。
我已经试过了，使用% 10和/ 10。
SELECT MIN([Date]) AS RecT, AVG(Value)
FROM [FRIIB].[dbo].[ArchiveAnalog]
GROUP BY (DATEPART(MINUTE, [Date]) / 10)
ORDER BY RecT
是否有可能使日期输出没有毫秒?
当前回答
select dateadd(minute, datediff(minute, 0, Date), 0),
sum(SnapShotValue)
FROM [FRIIB].[dbo].[ArchiveAnalog]
group by dateadd(minute, datediff(minute, 0, Date), 0)
其他回答
select dateadd(minute, datediff(minute, 0, Date), 0),
sum(SnapShotValue)
FROM [FRIIB].[dbo].[ArchiveAnalog]
group by dateadd(minute, datediff(minute, 0, Date), 0)
如果你想实际显示日期，有一个变量分组，并能够指定大于60分钟的时间框架:
DECLARE @minutes int
SET @minutes = 90
SELECT
DATEADD(MINUTE, DATEDIFF(MINUTE, 0, [Date]) / @minutes * @minutes, 0) as [Date],
AVG([Value]) as [Value]
FROM [FRIIB].[dbo].[ArchiveAnalog]
GROUP BY
DATEDIFF(MINUTE, 0, [Date]) / @minutes
终于讲完了
GROUP BY
DATEPART(YEAR, DT.[Date]),
DATEPART(MONTH, DT.[Date]),
DATEPART(DAY, DT.[Date]),
DATEPART(HOUR, DT.[Date]),
(DATEPART(MINUTE, DT.[Date]) / 10)
MySql:
GROUP BY
DATE(`your_date_field`),
HOUR(`your_date_field`),
FLOOR( MINUTE(`your_date_field`) / 10);
在SQLite中，为了按小时分组，你可以这样做:
GROUP BY strftime('%H', [FRIIB].[dbo].[ArchiveAnalog].[Date]);
并按每10分钟分组:
GROUP BY strftime('%M', [FRIIB].[dbo].[ArchiveAnalog].[Date]) / 10;
