Как создать временную таблицу со строкой для каждого из последних 24 часов?

#sql

Вопрос:

В принципе, я хочу запрос, который генерировал бы результат со строкой для каждого часа за последние 24 часа:

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

Как я могу это сделать без курсоров и временных таблиц?

Ответ №1:

По одной строке на каждый час для заданной даты (решение SQL Server).

 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

1. 1 для прямого использования (недокументированных) значений spt_values . Я знал, что где-то в прошлом я делал что-то очень похожее, но могу ли я вспомнить, как или где? Бит «type = ‘P'» является ключевым.

Ответ №2:

Ну … на SQL Server вы могли бы это сделать…

 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

Другим, немного более изотерическим способом было бы использовать функцию ранжирования row_number для первых 24 строк некоторого вспомогательного объекта (например, 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

Ответ №3:

Вот простой способ…

 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.

Для конкретной платформы или решения конкретной проблемы может быть лучший способ, но вам придется предоставить более подробную информацию, чтобы получить этот ответ.