日期:2014-05-17 浏览次数:20701 次
with tb(id,标识,起始时间,截至时间)as(
select 1,'a','2013-7-02','2013-7-18' union
select 2,'B','2013-7-19','2013-7-25' union
select 3,'C','2013-7-30','2013-8-15'
),tc as(
select a=DATEADD(day,number,起始时间) from tb,master..spt_values
where type='p' and DATEADD(day,number,起始时间)<=截至时间
),td as(
select distinct a=dateadd(day,number,(select MIN(起始时间) from tb)) from tb,master..spt_values
where type='p'
and dateadd(day,number,(select MIN(起始时间) from tb))<=(select max(截至时间)from tb)
)
select td.a 日期,case when tc.a IS null then '' else '有' end 标识
from td left join tc on td.a=tc.a
order by 1
create table Test_123
(
id int ,
表示 char(1),
起始时间 date,
截止时间 date
)
insert into Test_123 values (1,'A', '2013-7-2',' 2013-7-18' )