如何在 SQL Server 中生成日期范围 [英] How to generate a range of dates in SQL Server

问题描述

标题没有完全表达我的意思，这可能是重复的.

The title doesn't quite capture what I mean, and this may be a duplicate.

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

Here's the long version: given a guest's name, their registration date, and their checkout date, how do I generate one row for each day that they were a guest?

例如:Bob 在 7/14 签到并在 7/17 离开.我要

Ex: Bob checks in 7/14 and leaves 7/17. I want

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

作为我的结果.

谢谢！

推荐答案

我认为对于这个特定目的，下面的查询与使用专用查找表一样有效.

I would argue that for this specific purpose the below query is about as efficient as using a dedicated lookup table.

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

<小时>

大概你需要把它作为一个集合，而不是单个成员，所以这里有一种适应这种技术的方法:

---

Presumably you'll need this as a set, not for a single member, so here is a way to adapt this technique:

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 指出的，这可以简化为:

As @Dems pointed out, this could be simplified to:

;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);

这篇关于如何在 SQL Server 中生成日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！