标题并没有完全体现我的意思,这可能是重复的.
这是长版本：给定一个客人的姓名,他们的注册日期和结账日期,我如何为他们作为客人的每一天生成一行？
例如：鲍勃在7月14日检查并离开7月17日.我想要
('Bob',7/14),('Bob',7/15),7/16),7/17)
作为我的结果.
谢谢！
解决方法
我认为,出于这个特定目的,下面的查询与使用专用查找表一样有效.
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,(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);
