Вопрос по sql-server, sql, tsql – Как создать диапазон дат в SQL Server

7

Название не совсем соответствует тому, что я имею в виду, и это может быть дубликатом.

Вот длинная версия: учитывая имя гостя, дату его регистрации и дату оформления заказа, как мне создать одну строку для каждого дня, когда он был гостем?

Пример: Боб проверяет 7/14 и уезжает 7/17. я хочу

('Bob', 7/14), ('Bob', 7/15), ('Bob', 7/16), ('Bob', 7/17) 

как мой результат.

Спасибо!

Пожалуйста, укажите версию SQL Server. Я опубликовал решение, которое зависит от SQL Server 2008; это может отличаться, если вы используете SQL Server 2005. Aaron Bertrand
Как правило, вы не делаете. У вас есть таблица поиска и забрать их оттуда.WHERE calendar.date >= user.start_date AND calendar.date <= user.leave_date  ВыCAN генерировать множества с помощью циклов или рекурсивных запросов, но они никогда не бывают такими же быстрыми, как при использовании справочной таблицы. MatBailie
Я задал очень похожий вопрос, но у меня были часы, а не дни. Вы можете изменить, чтобы соответствовать вашим потребностям довольно легко.stackoverflow.com/questions/10986344/… Limey
@Dems Это хороший ответ. Я надеялся обнаружить, что в SQL существует языковая конструкция, которая позволяет легко генерировать диапазон. Daniel Cotter

Ваш Ответ

5   ответов
-4

Я хотел бы создать триггер для создания дополнительных записей и запустить его при оформлении заказа. Кроме того, вы можете выполнять ежедневную полночную работу, выполняя то же самое (если вам нужна актуальная информация в вашей базе данных).

Если бы это было так тривиально, ОП не спрашивал бы, верно? И это не должно быть трудно на самом деле подкрепить ваш ответ с некоторым кодом дляthis язык?
@AaronBertrand - это тривиальная задача программирования на любом языке.
@Andy ... Вы написали & quot; @AaronBertra, и это тривиальная задача программирования на любом языке & quot ;. Давайте посмотрим, что у вас есть. Разместите код триггера.
На самом деле это не ответ - как триггер "создает дополнительные записи"?
Я предполагаю, что мы интерпретируем, "как мне генерировать одну строку для каждого дня, чтобы они были гостем" по-другому. & Lt; & развести руки GT; Для меня это звучит как вопрос о конкретном синтаксисе, а не «напиши запрос».
6

Я обычно делаю это с помощью трюка, используя row_number () в некоторой таблице. Так:

select t.name, dateadd(d, seq.seqnum, t.start_date)
from t left outer join
     (select row_number() over (order by (select NULL)) as seqnum
      from t
     ) seq
     on seqnum <= datediff(d, t.start_date, t.end_date)

Расчет для seq идет довольно быстро, так как никаких расчетов или упорядочения не требуется. Тем не менее, вы должны быть уверены, что таблица достаточно велика для всех промежутков времени.

0

Это может работать для вас:

with mycte as
 (
     select cast('2000-01-01' as datetime) DateValue, 'Bob' as Name
     union all
     select DateValue + 1 ,'Bob' as Name
     from    mycte   
     where   DateValue + 1 < '2000-12-31'
 )
 select *
from    mycte
OPTION (MAXRECURSION 0)
Это содержит «Подсчет рекурсивного CTE». См. Следующую статью, чтобы узнать, почему они так плохи даже при подсчете небольших чисел.sqlservercentral.com/articles/T-SQL/74118
1

Если у вас есть "Tally" или "Числа" стол, жизнь становится действительно простой для таких вещей.

 SELECT Member, DatePresent = DATEADD(dd,t.N,RegistrationDate)
   FROM @t 
  CROSS JOIN dbo.Tally t
  WHERE t.N BETWEEN 0 AND DATEDIFF(dd,RegistrationDate,CheckoutDate)
;

Вот как создать "Tally" Таблица.

--===================================================================
--      Create a Tally table from 0 to 11000
--===================================================================
--===== Create and populate the Tally table on the fly.
 SELECT TOP 11001
        IDENTITY(INT,0,1) AS N
   INTO dbo.Tally
   FROM Master.sys.ALL_Columns ac1
  CROSS JOIN Master.sys.ALL_Columns ac2
;
--===== Add a CLUSTERED Primary Key to maximize performance
  ALTER TABLE dbo.Tally
    ADD CONSTRAINT PK_Tally_N 
        PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
;
--===== Allow the general public to use it
  GRANT SELECT ON dbo.Tally TO PUBLIC
;
GO

Для получения дополнительной информации о том, что такое "Tally" таблица написана на языке SQL и как ее можно использовать для замены циклов while и «скрытого RBAR»; рекурсивных CTE, которые учитываются, см. в следующей статье.

http://www.sqlservercentral.com/articles/T-SQL/62867/

26

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

DECLARE @start DATE, @end DATE;
SELECT @start = '20110714', @end = '20110717';

;WITH n AS 
(
  SELECT TOP (DATEDIFF(DAY, @start, @end) + 1) 
    n = ROW_NUMBER() OVER (ORDER BY [object_id])
  FROM sys.all_objects
)
SELECT 'Bob', DATEADD(DAY, n-1, @start)
FROM n;

Результаты:

Bob     2011-07-14
Bob     2011-07-15
Bob     2011-07-16
Bob     2011-07-17

Предположительно, вам понадобится это как набор, а не для одного участника, так что вот способ адаптировать эту технику:

DECLARE @t TABLE
(
    Member NVARCHAR(32), 
    RegistrationDate DATE, 
    CheckoutDate DATE
);

INSERT @t SELECT N'Bob', '20110714', '20110717'
UNION ALL SELECT N'Sam', '20110712', '20110715'
UNION ALL SELECT N'Jim', '20110716', '20110719';

;WITH [range](d,s) AS 
(
  SELECT DATEDIFF(DAY, MIN(RegistrationDate), MAX(CheckoutDate))+1,
    MIN(RegistrationDate)
    FROM @t -- WHERE ?
),
n(d) AS
(
  SELECT DATEADD(DAY, n-1, (SELECT MIN(s) FROM [range]))
  FROM (SELECT ROW_NUMBER() OVER (ORDER BY [object_id])
  FROM sys.all_objects) AS s(n)
  WHERE n <= (SELECT MAX(d) FROM [range])
)
SELECT t.Member, n.d
FROM n CROSS JOIN @t AS t
WHERE n.d BETWEEN t.RegistrationDate AND t.CheckoutDate;
----------^^^^^^^ not many cases where I'd advocate between!

Результаты:

Member    d
--------  ----------
Bob       2011-07-14
Bob       2011-07-15
Bob       2011-07-16
Bob       2011-07-17
Sam       2011-07-12
Sam       2011-07-13
Sam       2011-07-14
Sam       2011-07-15
Jim       2011-07-16
Jim       2011-07-17
Jim       2011-07-18
Jim       2011-07-19

Как отметил @Dems, это можно упростить до:

;WITH natural AS 
(
  SELECT ROW_NUMBER() OVER (ORDER BY [object_id]) - 1 AS val 
  FROM sys.all_objects
) 
SELECT t.Member, d = DATEADD(DAY, natural.val, t.RegistrationDate) 
  FROM @t AS t INNER JOIN natural 
  ON natural.val <= DATEDIFF(DAY, t.RegistrationDate, t.CheckoutDate);
@Dems, когда я начал писать, моей целью было использовать самый высокий диапазон вTOP противsys.all_objects, Вы правы в том, что это можно упростить.
Спасибо, ваш запрос делает то, что я искал. Один вопрос - нужно ли использовать MAX и MIN в «диапазоне»? Таблица? В этом примере я вижу только одну строку, сгенерированную для «диапазона», поэтому есть только один кандидат на максимальное или минимальное значение (в этом случае я бы просто поместил диапазон и дату начала в обычные переменные). Я весьма впечатлен вашими предпочтениями SQL и любопытно, есть ли здесь какая-то тонкость, которую я пропускаю. Daniel Cotter
Это предназначено для случая, когда вы имеете дело с более чем одним пользователем, и даты могут совпадать. Если вы имеете дело только с одним посещением одного пользователя, тогда вам вообще не нужно использовать эту версию запроса.
Оптимизатор AFAIK SQL Server означает, что вам действительно не нужноWHERE n < = (SELECT MAX()) что означает, что это может быть еще более упрощено ...WITH natural AS (SELECT ROW_NUMBER() OVER (ORDER BY id) - 1 AS val FROM sys.objects) SELECT t.Member, DATEADD(DAY, natural.val, t.start) FROM @t AS t INNER JOIN natural ON natural.val <= DATEDIFF(DAY, t.start, t.end) [But, even then, a straight look-up table is still going to use less CPU cycles at the very least.]
Хм. Наша производственная коробка с тысячами гостей и перекрывающимися датами все еще возвращает только одну строку для [range]. Daniel Cotter

Похожие вопросы