如何在SQL Server中生成日期范围
如何在SQL Server中生成日期范围
提问于 2012-06-21 23:25:57
标题并不能完全理解我的意思，这可能是一个复制品。
下面是详细版本:给定客人的姓名、注册日期和结账日期，如何为他们作为客人的每一天生成一行？
例:鲍勃7/14报到，7/17离开。我想
('Bob', 7/14), ('Bob', 7/15), ('Bob', 7/16), ('Bob', 7/17) 这就是我的结果。
谢谢!
回答 5
Stack Overflow用户
回答已采纳
发布于 2012-06-21 23:35:08
我认为，对于这个特定的目的，下面的查询与使用专用查找表的效率差不多。
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假设您将需要它作为一个集合，而不是单个成员，所以这里有一种方法来适应这种技术：
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指出的那样，这可以简化为：
;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);Stack Overflow用户
发布于 2012-06-21 23:37:11
我通常在一些表上使用row_number()的技巧来完成这项工作。所以：
select t.name, dateadd(d, seq.seqnum, t.start_date)
from t left outer join
(select row_number() over (order by (select NULL)) as seqnum
from t
) seq
on seqnum <= datediff(d, t.start_date, t.end_date)seq的计算速度非常快，因为不需要计算或排序。但是，您需要确保该表足够大，可以容纳所有时间跨度。
Stack Overflow用户
发布于 2012-06-25 12:54:16
如果你有一个“计数”或“数字”表，对于这样的事情来说，生活变得非常简单。
SELECT Member, DatePresent = DATEADD(dd,t.N,RegistrationDate)
FROM @t
CROSS JOIN dbo.Tally t
WHERE t.N BETWEEN 0 AND DATEDIFF(dd,RegistrationDate,CheckoutDate)
;下面是如何构建一个"Tally“表。
--===================================================================
-- Create a Tally table from 0 to 11000
--===================================================================
--===== Create and populate the Tally table on the fly.
SELECT TOP 11001
IDENTITY(INT,0,1) AS N
INTO dbo.Tally
FROM Master.sys.ALL_Columns ac1
CROSS JOIN Master.sys.ALL_Columns ac2
;
--===== Add a CLUSTERED Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
;
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
;
GO有关SQL中什么是"Tally“表以及如何使用它来替换While循环和递归CTE的”隐藏RBAR“的更多信息，请参阅下面的文章。
http://www.sqlservercentral.com/articles/T-SQL/62867/
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/11141507
复制相关文章
相似问题
腾讯云开发者
