在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中文網其他相關文章!