select t1.student_id,t1.insertDate, min(t2.insertDate) from table1 t1
join table1 t2 on( t1.student_id= t2.student_id and t1.insertDate < t2.insertDate)
group by t1.student_id,t1.insertDate
with tmp as
(
select 19464318 student_id,'2012-06-20 07:52:52.043' insertDate
union all
select 19464318 student_id,'2012-11-27 06:36:08.570' insertDate
union all
select 19464318 student_id,'2015-08-17 11:30:35.340' insertDate
)
select t1.student_id,t1.insertDate,t2.insertDate
from tmp t1
cross apply (select top 1 * from tmp t2 where t1.student_id = t2.student_id
and t1.insertDate < t2.insertDate order by insertDate) as t2
4 个回复
seng - 从事BI、大数据、数据分析工作 2015-09-21 回答
赞同来自:
join table1 t2 on( t1.student_id= t2.student_id and t1.insertDate < t2.insertDate)
group by t1.student_id,t1.insertDate
子建 - Kettle讲师、顾问、数据仓库架构 2015-09-21 回答
赞同来自: 悟 、BAO胖子
悟 - 取是能力,舍是境界 2015-09-21 回答
赞同来自:
BAO胖子 - 15年BI经验,涉足电力,快消品,医药,信息服务等行业的BI老兵。 2015-09-21 回答
赞同来自:
1. 按student id为key,对时间做order by,求出row number, select row_number() over ... 的路子
2. 然后 row_number = row_number + 1的模式做join,把row_number对应时间放前面start_date,row_number+1放end_date
可以试试这个思路,如果搞不定我再自己试试写这段code