首頁 >資料庫 >mysql教程 >如何在 SQL Server 中產生多個客人入住的每日日期範圍?

如何在 SQL Server 中產生多個客人入住的每日日期範圍?

Patricia Arquette
Patricia Arquette原創
2025-01-10 09:46:40353瀏覽

How to Generate a Daily Date Range for Multiple Guests' Stays in SQL Server?

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

陳述:
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn