我必须在sql server中跟随表：
date | status 2009-01-01 12:00:00 OK 2009-01-01 12:03:00 Failed 2009-01-01 12:04:00 OK 2009-01-01 12:06:20 OK 2009-01-01 12:07:35 Failed 2009-01-01 12:07:40 Failed 2009-01-01 12:20:40 Failed 2009-01-01 12:25:40 OK
我需要以下内容：从2009年1月1日12:00开始,每隔10分钟,我需要查看OK和Failed的数量.
就像是：
INTERVAL Failed OK 2009-01-01 12:00:00-2009-01-01 12:15:00 1 2 2009-01-01 12:15:01-2009-01-01 12:30:00 0 1
等等..
解决方法
好的,首先..
你提到10分钟,并提供一个15分钟的例子..另外你的样本数据应该返回不同于你发布的结果..
解决方案使用Pivot
Declare @datetimestart datetime
Declare @interval int
Set @datetimestart = '2009-01-01 12:00:00'
Set @interval = 15
Select
*
From
(
Select
DateAdd( Minute,Floor(DateDiff(Minute,@datetimestart,[date])/@interval)*@interval,@datetimestart),DateAdd( Minute,@interval + Floor(DateDiff(Minute,status
From dtest
) As W([from],[to],status)
Pivot (Count(status) For status In ([ok],[Failed])) p
这将回来
From To Ok Failed 2009-01-01 12:00:00.000 2009-01-01 12:15:00.000 3 3 2009-01-01 12:15:00.000 2009-01-01 12:30:00.000 1 0
评论后更新
此版本将包含数据库中没有值的时间间隔.
我们需要动态创建一个临时表..
Declare @datetimestart datetime,@datetimeend datetime,@datetimecurrent datetime
Declare @interval int
Set @datetimestart = '2009-01-01 12:00:00'
Set @interval = 10
Set @datetimeend = (Select max([date]) from dtest)
SET @datetimecurrent = @datetimestart
declare @temp as table ([from] datetime,[to] datetime)
while @datetimecurrent < @datetimeend
BEGIN
insert into @temp select (@datetimecurrent),dateAdd( minute,@interval,@datetimecurrent)
set @datetimecurrent = dateAdd( minute,@datetimecurrent)
END
Select
*
From
(
Select
[from],status
From @temp t left join dtest d on d.[date] between t.[from] and t.[to]
) As W([from],status)
Pivot (Count(status) For status In ([ok],[Failed])) p
现在使用10分钟的间隔,显示没有值的句点,返回..
From To Ok Failed 2009-01-01 12:00:00.000 2009-01-01 12:10:00.000 3 3 2009-01-01 12:10:00.000 2009-01-01 12:20:00.000 0 0 2009-01-01 12:20:00.000 2009-01-01 12:30:00.000 1 0
