;with cte1as( select ID, Date, row_number()over(partition by ID order by Date) groupID from #test),cte2as( select ID, Date, Date as DateTmp, groupID, 1 as getRow from cte1 where groupID=1 union all select c1.ID, c1.Date, case when datediff(Day, c2.DateTmp, c1.Date) > 30 then c1.Date else c2.DateTmp end as DateTmp, c1.groupID, case when datediff(Day, c2.DateTmp, c1.Date) > 30 then 1 else 0 end as getRow from cte1 c1 inner join cte2 c2 on c2.groupID+1=c1.groupID and c2.ID=c1.ID)select ID, Date from cte2 where getRow=1 order by ID, Date
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)