使用CTE时开始日期和结束日期中的问题

Question

我有下面的输入

ID  Activity    Date
1   gardening   2011-01-01 00:00:00.000
1   gardening   2011-02-01 00:00:00.000
2   cooking 2011-03-01 00:00:00.000
2   cooking 2011-04-01 00:00:00.000
2   cooking 2011-05-01 00:00:00.000
1   gardening   2011-06-01 00:00:00.000
1   gardening   2011-07-01 00:00:00.000

ddl如下所示

Declare @t table(ID int,Activity Varchar(50),[Date] DATETIME)
Insert into @t Select 1,'gardening','01/01/2011' union all Select 1,'gardening','02/01/2011' 
union all Select 2,'cooking','03/01/2011' union all Select 2,'cooking','04/01/2011'
union all Select 2,'cooking','05/01/2011' union all Select 1,'gardening','06/01/2011'
union all Select 1,'gardening','07/01/2011' 

select * from @t 

预期输出

ID ACTIVITY     INITIAL_DATE  END_DATE 
1  gardening    01/01/2011    02/01/2011 
1  gardening    02/01/2011    06/01/2011 
1  gardening    06/01/2011    07/01/2011 
2  cooking      03/01/2011    04/01/2011 
2  cooking      04/01/2011    05/01/2011 

到目前为止我已经做了

;with cte as(Select Rn= ROW_NUMBER() Over(order by ID,[Date]),* from @t)
,cte2 as(
Select Rn
,ID,Activity,InitialDate =[Date],EndDate = [Date] 
from cte where Rn =1
union all
Select c1.Rn
,c1.ID,c1.Activity,c1.Date,c1.Date
from cte2 c2
join cte c1
on c1.rn  = c2.Rn+1

)
select ID,Activity,InitialDate,EndDate from cte2

但是输出是不正确的

ID  Activity    InitialDate EndDate
1   gardening   2011-01-01 00:00:00.000 2011-01-01 00:00:00.000
1   gardening   2011-02-01 00:00:00.000 2011-02-01 00:00:00.000
1   gardening   2011-06-01 00:00:00.000 2011-06-01 00:00:00.000
1   gardening   2011-07-01 00:00:00.000 2011-07-01 00:00:00.000
2   cooking 2011-03-01 00:00:00.000 2011-03-01 00:00:00.000
2   cooking 2011-04-01 00:00:00.000 2011-04-01 00:00:00.000
2   cooking 2011-05-01 00:00:00.000 2011-05-01 00:00:00.000

需要帮助

Answer 1

;with cte as 
(
  select *,
         row_number() over(partition by ID order by [Date]) as rn
  from @t         
)
select C1.ID,
       C1.Activity,
       C1.[Date] as INITIAL_DATE,
       C2.[Date] as END_DATE
from cte as C1
  inner join cte as C2
    on C1.ID = C2.ID and
       C1.rn + 1 = C2.rn
order by C1.ID, C1.[Date]  

Answer 2

试试这个-

在甲骨文中，它提供了想要的输出。请检查oracle数据库中铅()的相应sql server函数。

with cte as(Select * from @t) 
(
SELECT * from 
(
SELECT id,activity,
   lead(date) over(partition be id,activity order by date desc) INITIAL_DATE,
   date END_DATE  
from cte 
order by id,activity,date
)
WHERE INITIAL_DATE is not null 
)