基于while循环的SQL查询

Question

我正在尝试使用 while 循环构建一个 sql 查询，每次迭代将日期时间增加一分钟，然后根据时间生成一个 select 语句：

declare @dt datetime
set @dt = '2011-7-21'
while @dt < '2011-7-22'
begin
    select Count(*) From Actions Where Timestamp = @dt
    set @dt = DATEADD(mi, 1, @dt)
end

该查询按预期工作，除了 while 循环的每次迭代似乎都会产生一个新的完全查询，而不是简单地查询一个新行。有没有一种方法可以构造这个，以便它的一个查询和每一行都是通过循环的增量生成的？ 我相信发生这种情况是因为 select 语句位于循环内部，但我不确定如何以不同的方式构建它。

编辑-这是我使用临时表想到的，但它很慢。也许有更快的方法？如果不是这样也没关系，至少这是有效的：

create table #temp 
(
  [DT] datetime not null,
  [Total] int not null
)

declare @dt datetime
declare @result int
set @dt = '2011-7-21'
while @dt < '2011-7-22'
begin
    set @result = Count(*) From Actions Where Timestamp = @dt
    insert #temp ([DT],[Total]) values (@dt, @result)
    set @dt = DATEADD(mi, 1, @dt)
end

select * from #temp;

drop table #temp; 

Answer 1

使用数字表的一种方法

declare @dt datetime
set @dt = '2011-07-21'

select DATEADD(mi, number, @dt) 
from master..spt_values
where type = 'P'
and DATEADD(mi, number, @dt)  < '2011-07-22'

如果您有自己的数字表，请使用该表

请参阅此处了解更多信息 http://wiki.lessthandot.com/index.php/Date_Ranges_Without_Loops

你的完整查询就像

DECLARE @dt DATETIME
SET @dt = '2011-07-21'

SELECT x.SomeTime,y.TheCount FROM 
(SELECT DATEADD(mi, number, @dt) as SomeTime FROM master..spt_values
WHERE TYPE = 'P'
AND DATEADD(mi, number, @dt)  < '2011-07-22') x
LEFT JOIN (
    SELECT TIMESTAMP, COUNT(*) AS TheCount
    FROM Actions
    GROUP BY TIMESTAMP
) AS y
ON x.SomeTime = dateadd(mi, datediff(mi, 0, y.Timestamp)+0, 0)

Answer 2

如果您有一个数字表（从 0 到一百万或其他），这相对简单：

SELECT *
FROM Numbers AS n
LEFT JOIN (
    SELECT Timestamp, COUNT(*) AS Ct
    FROM Actions
    GROUP BY Timestamp
) AS ActionSummary
    ON ActionSummary.Timestamp = DATEADD(mi, n.Number, '2011-07-21')
WHERE DATEADD(mi, n.Number, '2011-07-21') < '2011-07-22'
ORDER BY DATEADD(mi, n.Number, '2011-07-21')

不需要循环。

有一些方法可以优化它，但这应该是可以理解的。

另请注意，时间戳不能有任何秒或几分之一秒才能正常工作（您的原始版本也有此问题）。