遞迴

[test104]

NAME STARTDAY ENDDAY
------------------------------------------------------
A 2018-04-01 2018-04-02
B 2018-04-02 2018-04-03


with A as(
select Name,StartDay NowDay,StartDay,EndDay from (
select 'A' Name ,'2018-04-01' StartDay,'2018-04-02' EndDay
union ALL
select 'B' Name ,'2018-04-02' StartDay,'2018-04-03' EndDay) as M
UNION ALL
SELECT Name,NowDay,StartDay,EndDay
FROM A
WHERE (DATEADD(d, 1, StartDay) <EndDay))select * from A

我希望SELECT 結果

NAME NowDay STARTDAY ENDDAY
------------------------------------------------------
A 2018-04-01 2018-04-01 2018-04-02
B 2018-04-02 2018-04-02 2018-04-03
B 2018-04-03 2018-04-02 2018-04-03

[RINK_1]

if OBJECT_ID(N'tempdb..#T') is not null
drop table #T
go

create table #T
(name varchar(10),
startday date,
endday date)

insert into #T
select 'A','2018-04-01','2018-04-02' union all
select 'B','2018-04-02','2018-04-03' union all
select 'A','2018-04-03','2018-04-04'

with cte
as
(select A.number,B.min_startday 
from master.dbo.spt_values A
join
(select datediff(day,MIN(startday) ,MAX(endday)) as date_len,MIN(startday) as min_startday from #T) as B ON A.number<=B.date_len
where type='P')

select ISNULL(B.name,C.name) AS NAME,
       DATEADD(DAY,number,A.min_startday) AS NOWDAY,
       ISNULL(B.startday,C.startday) AS STARTDAY,
       ISNULL(B.endday,C.endday) AS ENDDAY
from cte A
left join #T B on B.startday=DATEADD(DAY,number,A.min_startday)
left join #T C on C.startday=DATEADD(DAY,-1,DATEADD(DAY,number,A.min_startday))

[mingqing6364]

with A as(
select Name,StartDay NowDay,StartDay,EndDay from (
select 'A' Name ,'2018-04-01' StartDay,'2018-04-02' EndDay
union ALL
select 'B' Name ,'2018-04-02' StartDay,'2018-04-03' EndDay) as M
UNION ALL
SELECT         Name,CONVERT(VARCHAR(10),DATEADD(d, 1, NowDay),120),StartDay,EndDay
FROM              A
WHERE          (DATEADD(d, 1, NowDay) <= EndDay))

select * from A ORDER BY Name, NowDay

这样？？？