如何获取表中两个日期字段区间的所有的日期。
例如：
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
------解决思路----------------------
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 行受影响)