如上图所示:
1、错位相减:time2的时间减去time1的时间,比如“2012-03-19 10:18:53” - “2012-03-19 10:09:33”
2、排序方式是 time1 desc
3、time1 desc 排序后,ID与A的内容不一定是按照顺序下来的,但一定是从大到小排下来的。
比较啰嗦
------解决方案--------------------
- SQL code
with tb as(select 12 ID, 'D203456700' A, '2012-03-19 10:08:01' Time1,'2012-03-19 10:10:01' Time2 from dual union allselect 11, 'D203456600' ,'2012-03-19 10:06:01' ,'2012-03-19 10:08:01' from dual union allselect 9 ,'D203456500' ,'2012-03-19 10:04:01' ,'2012-03-19 10:06:01' from dual union allselect 8 ,'D203456300' ,'2012-03-19 10:02:01' ,'2012-03-19 10:04:01' from dual)select prior to_date(time2,'yyyy-mm-dd hh24:mi:ss')- to_date(time1,'yyyy-mm-dd hh24:mi:ss') from tb start with Time2='2012-03-19 10:10:01' connect by Time2=prior Time1
------解决方案--------------------