求助，如何实现不同行列单元格之间的对比？

[nothk]

现有表：
这个表是按student_id和begin_date排序的，
student_id begin_date end_date
1001 2018-01-01 2018-01-15
1001 2018-01-15 2018-02-10
1001 2018-02-11 2018-03-20
1002 2018-01-01 2018-01-15
1002 2018-02-01 2018-03-10
1002 2018-03-09 2018-03-20

希望生成的表：
student_id begin_date end_date times
1001 2018-01-01 2018-03-20 1
1002 2018-01-01 2018-01-15 1
1002 2018-02-01 2018-03-20 2

希望实现：
begin_date是培训开始时间，end_date是培训结束时间，

如果同一个student_id的相邻记录，第一行的end_date和第二行的begin_date相差大于等于3天，则认为这是两次培训，否则认为是一次培训，

比如student_id为1001，一共有3条记录，第二行的date_begin和第一行的date_end相差小于3天，第三行的date_begin和第二行的date_end相差也小于3天，我们就把这3条记录合为一条，

比如student_id为1002，第二行的date_begin（2月1日）和第一行的date_end（1月15日）相差大于3天，认为这是两次培训，

生成表的times列表示该学生第N次培训，

请问一下这个要如何实现，
向大神学习，

[卖水果的net]

楼主哪个版本的库，2012 + 有 lag 和 lead ，你研究 一下。

[RINK_1]

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

create table #T
(student_id varchar(5),
 begin_date date,
 end_date date)

insert into #T
select '1001','2018-01-01','2018-01-15' union all
select '1001','2018-01-20','2018-02-10' union all
select '1001','2018-02-15','2018-03-20' union all
select '1002','2018-01-01','2018-01-15' union all
select '1002','2018-01-16','2018-03-10' union all
select '1002','2018-03-15','2018-03-20' union all
select '1002','2018-03-21','2018-03-21' union all
select '1002','2018-03-25','2018-03-30' 

with cte_1
as
(select *,ROW_NUMBER() over (partition by student_id order by begin_date) as seq from #T),

cte_2
as
(select A.*,isnull(DATEDIFF(DAY,B.end_date,A.begin_date),0) as diff 
 from cte_1 A
 left join cte_1 B on A.student_id=B.student_id and A.seq=B.seq+1
),

cte_3
as
(select * from cte_2 where ABS(diff)>3 or seq=1)

select A.student_id,A.begin_date,C.end_date,ROW_NUMBER() over (partition by A.student_id order by A.begin_date) as rn
from cte_3 as A
outer apply 
(select top 1 * from cte_3 where A.student_id=student_id and seq>A.seq order by seq) as B
outer apply 
(select top 1 * 
 from cte_2 where student_id=A.student_id 
                  and ((ISNULL(B.student_id,'')<>'' and seq=B.seq-1) 
                  or ((ISNULL(B.student_id,'')='' and seq>=A.seq)))
                  order by seq desc) as C

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([student_id] int,[begin_date] Date,[end_date] Date)
Insert #T
select 1001,'2018-01-01','2018-01-15' union all
select 1001,'2018-01-15','2018-02-10' union all
select 1001,'2018-02-11','2018-03-20' union all
select 1002,'2018-01-01','2018-01-15' union all
select 1002,'2018-02-01','2018-03-10' union all
select 1002,'2018-03-09','2018-03-20'
Go
--测试数据结束
;WITH ctea AS (
Select *,ROW_NUMBER()OVER(PARTITION BY student_id ORDER BY end_date) rn from #T
),cteb AS (
SELECT a.student_id ,
	   a.begin_date ,
	   a.end_date ,
       DATEDIFF(DAY, a.end_date, b.begin_date) AS diff
FROM ctea a
    LEFT JOIN ctea b
        ON b.student_id = a.student_id
           AND b.rn = a.rn + 1)
,ctec AS (
SELECT cteb.student_id,
       MIN(cteb.begin_date) AS begin_date,
       MAX(cteb.end_date) AS end_date
FROM cteb
WHERE cteb.diff < 3
GROUP BY cteb.student_id
UNION
SELECT cteb.student_id,
       cteb.begin_date,

[二月十六]

上边的稍微有点问题

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([student_id] int,[begin_date] Date,[end_date] Date)
Insert #T
select 1001,'2018-01-01','2018-01-15' union all
select 1001,'2018-01-15','2018-02-10' union all
select 1001,'2018-02-11','2018-03-20' union all
select 1002,'2018-01-01','2018-01-15' union all
select 1002,'2018-02-01','2018-03-10' union all
select 1002,'2018-03-09','2018-03-20'
Go
--测试数据结束
;WITH ctea AS (
Select *,ROW_NUMBER()OVER(PARTITION BY student_id ORDER BY end_date) rn from #T
),cteb AS (
SELECT a.student_id ,
	   a.begin_date ,
	   a.end_date ,
       DATEDIFF(DAY, a.end_date, b.begin_date) AS diff
FROM ctea a
    LEFT JOIN ctea b
        ON b.student_id = a.student_id
           AND b.rn = a.rn + 1)
,ctec AS (
SELECT cteb.student_id,
       MIN(cteb.begin_date) AS begin_date,
       MAX(cteb.end_date) AS end_date
FROM cteb
WHERE ISNULL(cteb.diff,0) < 3
GROUP BY cteb.student_id
UNION
SELECT cteb.student_id,