在SQL Server中生成日期范围
虽然标题暗示生成一系列日期，但主要问题是为客人入住机构的每一天创建多行。给定客人姓名、入住日期和退房日期，目标是为入住的每一天输出一行。
下面的查询有效地解决了这个任务：
<code class="language-sql">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;</code>执行此查询后，将生成以下结果（根据提供的示例）：
<code>Bob 2011-07-14 Bob 2011-07-15 Bob 2011-07-16 Bob 2011-07-17</code>
对于需要容纳多个客人的情况，可以将查询改编成更全面的形式：
<code class="language-sql">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
),
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;</code>此改编后的查询会生成以下结果，其中包含多个客人的数据：
<code>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</code>
以上是如何在 SQL Server 中生成多个客人入住的每日日期范围？的详细内容。更多信息请关注PHP中文网其他相关文章！
