Question

基本上,我想问一下,过去24小时每小时每小时增长一次会产生结果:

01/01/2011 00:00:00
01/01/2011 01:00:00
01/01/2011 02:00:00
...

Any way I can do that without cursors and temp tables?

Answer 1

One row for each hour for a given date (SQL Server solution).

select dateadd(hour, Number,  20110101 )
from master..spt_values
where type =  P  and
      number between 0 and 23

结果是每小时24小时每小时增长一次

select dateadd(hour, datediff(hour, 0, getdate()) - number, 0)
from master..spt_values
where type =  P  and
      number between 0 and 23

Answer 2

......在服务器上,你可以这样做......

WITH cte
AS
(
  SELECT CAST( 1-jan-2011  AS DATETIME) AS  date 
  UNION ALL
  SELECT DATEADD(hh, 1, [date]) FROM cte WHERE [date] <  1-jan-2011 23:00 
)
SELECT [date] FROM cte

......在现实中,一个仅限(0至23)的桌子将更为有用,因为你可以随时增加时间。

WITH cte
AS
(
  SELECT 0 as  Hour 
  UNION ALL
  SELECT hour + 1 FROM cte WHERE hour < 23
)
SELECT DateAdd(hh, hour,  1-jan-2010 ) FROM cte

Another, slightly more isoteric way would be to use the row_number ranking function against the first 24 rows of some abitrary object (like spt_values)...

WITH cte AS
(
  SELECT n
  FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY type ) FROM master..spt_values ) D ( n )
  WHERE n < 24
)
SELECT dateadd(hh,n, 01-jan-2011 ) FROM cte

Answer 3

Here is the simple way...

SELECT  01/01/2011 00:00:00  as [hour], blah, blah2
UNION ALL
SELECT  01/01/2011 01:00:00  as [hour], blah, blah2
UNION ALL
SELECT  01/01/2011 02:00:00  as [hour], blah, blah2
UNION ALL
...etc 24 times.

在某个特定平台或解决某个具体问题方面,也许会有更好的办法,但为了找到答案,你必须提供更多细节。

友情链接