如何在SQL Server中生成日期范围

Question

标题并不能完全反映我的意思，这可能是重复的。

这是一个很长的版本：给定客人的姓名，他们的注册日期和他们的退房日期，如何为他们每天作为客人生成一行？

例：鲍勃签入7/14，然后离开7/17。我想要

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

作为我的结果。

谢谢！

Answer 1

我认为，为此特定目的，以下查询与使用专用查找表一样有效。DECLARE @start DATE, @end DATE;SELECT @start = '20110714', @end = '20110717';;WITH n AS&nbsp;(&nbsp; SELECT TOP (DATEDIFF(DAY, @start, @end) + 1)&nbsp;&nbsp; &nbsp; n = ROW_NUMBER() OVER (ORDER BY [object_id])&nbsp; FROM sys.all_objects)SELECT 'Bob', DATEADD(DAY, n-1, @start)FROM n;结果：Bob&nbsp; &nbsp; &nbsp;2011-07-14Bob&nbsp; &nbsp; &nbsp;2011-07-15Bob&nbsp; &nbsp; &nbsp;2011-07-16Bob&nbsp; &nbsp; &nbsp;2011-07-17大概您需要将此作为一个集合，而不是单个成员，因此这里是一种适应此技术的方法：DECLARE @t TABLE(&nbsp; &nbsp; Member NVARCHAR(32),&nbsp;&nbsp; &nbsp; RegistrationDate DATE,&nbsp;&nbsp; &nbsp; 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&nbsp;(&nbsp; SELECT DATEDIFF(DAY, MIN(RegistrationDate), MAX(CheckoutDate))+1,&nbsp; &nbsp; MIN(RegistrationDate)&nbsp; &nbsp; FROM @t -- WHERE ?),n(d) AS(&nbsp; SELECT DATEADD(DAY, n-1, (SELECT MIN(s) FROM [range]))&nbsp; FROM (SELECT ROW_NUMBER() OVER (ORDER BY [object_id])&nbsp; FROM sys.all_objects) AS s(n)&nbsp; WHERE n <= (SELECT MAX(d) FROM [range]))SELECT t.Member, n.dFROM n CROSS JOIN @t AS tWHERE n.d BETWEEN t.RegistrationDate AND t.CheckoutDate;----------^^^^^^^ not many cases where I'd advocate between!结果：Member&nbsp; &nbsp; d--------&nbsp; ----------Bob&nbsp; &nbsp; &nbsp; &nbsp;2011-07-14Bob&nbsp; &nbsp; &nbsp; &nbsp;2011-07-15Bob&nbsp; &nbsp; &nbsp; &nbsp;2011-07-16Bob&nbsp; &nbsp; &nbsp; &nbsp;2011-07-17Sam&nbsp; &nbsp; &nbsp; &nbsp;2011-07-12Sam&nbsp; &nbsp; &nbsp; &nbsp;2011-07-13Sam&nbsp; &nbsp; &nbsp; &nbsp;2011-07-14Sam&nbsp; &nbsp; &nbsp; &nbsp;2011-07-15Jim&nbsp; &nbsp; &nbsp; &nbsp;2011-07-16Jim&nbsp; &nbsp; &nbsp; &nbsp;2011-07-17Jim&nbsp; &nbsp; &nbsp; &nbsp;2011-07-18Jim&nbsp; &nbsp; &nbsp; &nbsp;2011-07-19正如@Dems指出的那样，这可以简化为：;WITH natural AS&nbsp;(&nbsp; SELECT ROW_NUMBER() OVER (ORDER BY [object_id]) - 1 AS val&nbsp;&nbsp; FROM sys.all_objects)&nbsp;SELECT t.Member, d = DATEADD(DAY, natural.val, t.RegistrationDate)&nbsp;&nbsp; FROM @t AS t INNER JOIN natural&nbsp;&nbsp; ON natural.val <= DATEDIFF(DAY, t.RegistrationDate, t.CheckoutDate);

Answer 2

我通常在某些表上使用row_number（）来达到目的。所以：select t.name, dateadd(d, seq.seqnum, t.start_date)from t left outer join&nbsp; &nbsp; &nbsp;(select row_number() over (order by (select NULL)) as seqnum&nbsp; &nbsp; &nbsp; from t&nbsp; &nbsp; &nbsp;) seq&nbsp; &nbsp; &nbsp;on seqnum <= datediff(d, t.start_date, t.end_date)seq的计算非常快，因为不需要计算或排序。但是，您需要确保该表对于所有时间段都足够大。