c_ocav_no t_check_tm t_next
20100127330100010005 2010-1-27 14:28:02
20100127330100010005 2010-1-28 15:28:35
20100127330100010005 2010-1-28 15:40:56 9999-12-31
在t_next 中插入t_check_tm 的下个值 ,结果要:第一个空格是 2010-1-28 15:28:35,第二个空格是 2010-1-28 15:40:56
怎么写这个sql啊,请大家帮忙啊!!
20100127330100010005 2010-1-27 14:28:02
20100127330100010005 2010-1-28 15:28:35
20100127330100010005 2010-1-28 15:40:56 9999-12-31
在t_next 中插入t_check_tm 的下个值 ,结果要:第一个空格是 2010-1-28 15:28:35,第二个空格是 2010-1-28 15:40:56
怎么写这个sql啊,请大家帮忙啊!!
set t_next=(select min(t_check_tm) from xxx where t_check_tm>t.t_check_tm)
where t_next is null
-------------------- -------------------- --------------------
20100127330100010005 2010-1-27 14:28:02
20100127330100010005 2010-1-28 15:28:35
20100127330100010005 2010-1-28 15:40:56 9999-12-31已用时间: 00: 00: 00.00
13:59:37 tina@PRACTICE> select c_ocav_no,t_check_tm,nvl(t_next,lead(t_check_tm)over(partition by c_ocav_no order by t_check_tm)) t_next from tb;C_OCAV_NO T_CHECK_TM T_NEXT
-------------------- -------------------- --------------------
20100127330100010005 2010-1-27 14:28:02 2010-1-28 15:28:35
20100127330100010005 2010-1-28 15:28:35 2010-1-28 15:40:56
20100127330100010005 2010-1-28 15:40:56 9999-12-31已用时间: 00: 00: 00.00
上面的例子是查询的sql
Connected as billing
SQL>
SQL> with tab as
2 (
3 select '20100127330100010005' c_ocav_no, '2010-1-27 14:28:02' t_check_tm from dual union all
4 select '20100127330100010005' c_ocav_no, '2010-1-28 15:28:35' t_check_tm from dual union all
5 select '20100127330100010005' c_ocav_no, '2010-1-28 15:40:56' t_check_tm from dual
6 )
7 select c_ocav_no,t_check_tm,lead(t_check_tm,1,0)over(order by 1) t_next from tab
8 /C_OCAV_NO T_CHECK_TM T_NEXT
-------------------- ------------------ ------------------
20100127330100010005 2010-1-27 14:28:02 2010-1-28 15:40:56
20100127330100010005 2010-1-28 15:40:56 2010-1-28 15:28:35
20100127330100010005 2010-1-28 15:28:35 0SQL>
where lead(t_check_tm)over(partition by c_ocav_no order by t_check_tm ) is not null