如何获取表中两个日期字段区间的所有的日期

[木易随风]

如何获取表中两个日期字段区间的所有的日期。
例如：

DECLARE @TB AS TABLE ([住院号] VARCHAR(10),[开始时间] DATETIME,[停止时间] DATETIME)
INSERT INTO @TB([住院号],[开始时间],[停止时间])
SELECT '113332','2011-01-04 11:52:59.000','2011-01-05 21:18:00.000'
UNION SELECT '113131','2011-01-04 12:39:59.000','2011-01-06 21:14:00.000'
SELECT * FROM @TB

住院号        开始时间                    停止时间
---------- -----------------------		-----------------------
113332     2011-01-04 11:52:59.000		2011-01-05 21:18:00.000
113131     2011-01-04 12:39:59.000		2011-01-06 21:14:00.000

想得到如下的结果：

日期                     住院号
----------                ------
2011-01-04       113332
2011-01-05       113332
2011-01-04       113131
2011-01-05       113131
2011-01-06       113131

[xiaodongni]

with cte as 
(select 113332 as id,'2011-01-04 11:52:59.000' as begintime,'2011-01-05 21:18:00.000' as endtime
union all
select 113331 as id,'2011-01-04 11:52:59.000' as begintime,'2011-01-06 21:18:00.000' as endtime)
select id,convert(date,DATEADD(day,number,a.begintime)) as mydate from (select number from master..spt_values
where type='p') as b join  cte as a
on datediff(day,a.begintime,a.endtime)>=b.number

---查询结果
id          mydate
----------- ----------
113332      2011-01-04
113332      2011-01-05
113331      2011-01-04
113331      2011-01-05
113331      2011-01-06

(5 行受影响)

[沉默肥牛]

第一印象是写个函数，取开始日期，结束日期，用dateadd增加，然后while判断。。。。。。。

[木易随风]

引用 1 楼 alimake 的回复:

with cte as 
(select 113332 as id,'2011-01-04 11:52:59.000' as begintime,'2011-01-05 21:18:00.000' as endtime
union all
select 113331 as id,'2011-01-04 11:52:59.000' as begintime,'2011-01-06 21:18:00.000' as endtime)
select id,convert(date,DATEADD(day,number,a.begintime)) as mydate from (select number from master..spt_values
where type='p') as b join  cte as a
on datediff(day,a.begintime,a.endtime)>=b.number

---查询结果
id          mydate
----------- ----------
113332      2011-01-04
113332      2011-01-05
113331      2011-01-04
113331      2011-01-05
113331      2011-01-06

(5 行受影响)

[xiaodongni]

引用 3 楼 shuchong1983 的回复:

[quote=引用 1 楼 alimake 的回复:]

with cte as 
(select 113332 as id,'2011-01-04 11:52:59.000' as begintime,'2011-01-05 21:18:00.000' as endtime
union all
select 113331 as id,'2011-01-04 11:52:59.000' as begintime,'2011-01-06 21:18:00.000' as endtime)
select id,convert(date,DATEADD(day,number,a.begintime)) as mydate from (select number from master..spt_values
where type='p') as b join  cte as a
on datediff(day,a.begintime,a.endtime)>=b.number

---查询结果
id          mydate
----------- ----------
113332      2011-01-04
113332      2011-01-05
113331      2011-01-04
113331      2011-01-05
113331      2011-01-06

(5 行受影响)

[/quote] 有个小问题 最好把最后的连接条件改一下。datediff(day,b.number,begintime)<=a.endtime和SELECT保持一致。这样性能好点。