sql-server - sql查询解决以下问题

我必须在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:00开始，从该日期开始每隔10分钟，我需要查看“确定”和“失败”的数量。

就像是:

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

ETC..

什么是最好的方式来做到这一点在SQL？

最佳答案

好的，首先..

您提到了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,@datetimestart,[date])/@interval)*@interval
,@datetimestart)
, 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, @interval, @datetimecurrent)
END

Select
  *
From
  (
    Select
      [from],[to], status
    From @temp t left join dtest d on d.[date] between t.[from] and t.[to]
  ) As W([from],[to], 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