有一个表:工号 开始日期 结束日期
02050327 2011-01-06 2011-01-08
02110353 2011-01-06 2011-01-08
03090311 2011-01-06 2011-01-07
04020287 2011-01-06 2011-01-09想得到以下结果:工号 日期
02050327 2011-01-06
02050327 2011-01-07
02050327 2011-01-08
02110353 2011-01-06
02110353 2011-01-07
02110353 2011-01-08
03090311 2011-01-06
03090311 2011-01-07
04020287 2011-01-06
04020287 2011-01-07
04020287 2011-01-08
04020287 2011-01-09
谢谢各位!
02050327 2011-01-06 2011-01-08
02110353 2011-01-06 2011-01-08
03090311 2011-01-06 2011-01-07
04020287 2011-01-06 2011-01-09想得到以下结果:工号 日期
02050327 2011-01-06
02050327 2011-01-07
02050327 2011-01-08
02110353 2011-01-06
02110353 2011-01-07
02110353 2011-01-08
03090311 2011-01-06
03090311 2011-01-07
04020287 2011-01-06
04020287 2011-01-07
04020287 2011-01-08
04020287 2011-01-09
谢谢各位!
insert into tb select '02050327','2011-01-06','2011-01-08'
insert into tb select '02110353','2011-01-06','2011-01-08'
insert into tb select '03090311','2011-01-06','2011-01-07'
insert into tb select '04020287','2011-01-06','2011-01-09'
go
select a.工号,dateadd(dd,b.number,a.开始日期)日期
from tb a,master..spt_values b
where b.type='p' and dateadd(dd,b.number,a.开始日期)<=a.结束日期
go
drop table tb
/*
工号 日期
---------- -----------------------
02050327 2011-01-06 00:00:00.000
02050327 2011-01-07 00:00:00.000
02050327 2011-01-08 00:00:00.000
02110353 2011-01-06 00:00:00.000
02110353 2011-01-07 00:00:00.000
02110353 2011-01-08 00:00:00.000
03090311 2011-01-06 00:00:00.000
03090311 2011-01-07 00:00:00.000
04020287 2011-01-06 00:00:00.000
04020287 2011-01-07 00:00:00.000
04020287 2011-01-08 00:00:00.000
04020287 2011-01-09 00:00:00.000(12 行受影响)*/
insert into tb select '02050327','2011-01-06','2011-01-08'
insert into tb select '02110353','2011-01-06','2011-01-08'
insert into tb select '03090311','2011-01-06','2011-01-07'
insert into tb select '04020287','2011-01-06','2011-01-09'
go
;with cte as(
select * from tb
union all
select 工号,dateadd(dd,1,开始日期) as 开始日期,结束日期 from cte
where dateadd(dd,1,开始日期)<=结束日期
)select 工号,开始日期 as 日期 from cte order by 1,2
go
drop table tb
/*
工号 日期
---------- -----------------------
02050327 2011-01-06 00:00:00.000
02050327 2011-01-07 00:00:00.000
02050327 2011-01-08 00:00:00.000
02110353 2011-01-06 00:00:00.000
02110353 2011-01-07 00:00:00.000
02110353 2011-01-08 00:00:00.000
03090311 2011-01-06 00:00:00.000
03090311 2011-01-07 00:00:00.000
04020287 2011-01-06 00:00:00.000
04020287 2011-01-07 00:00:00.000
04020287 2011-01-08 00:00:00.000
04020287 2011-01-09 00:00:00.000(12 行受影响)*/