我有表statelist,记录如下:
listrecid state starttime
1 100 2006-12-24 16:45:28
2 101 2006-12-24 17:04:28
3 102 2006-12-24 18:45:28
4 103 2006-12-24 19:51:05
5 104 2006-12-25 09:11:52 我要得到的查询结果如下,多一个endtime字段,值为下一个状态(状态为有序的)starttime值:
listrecid state starttime endtime
1 100 2006-12-24 16:45:28 2006-12-24 17:04:28
2 101 2006-12-24 17:04:28 2006-12-24 18:45:28
3 102 2006-12-24 18:45:28 2006-12-24 19:51:05
4 103 2006-12-24 19:51:05 2006-12-25 09:11:52
5 104 2006-12-25 09:11:52 null
listrecid state starttime
1 100 2006-12-24 16:45:28
2 101 2006-12-24 17:04:28
3 102 2006-12-24 18:45:28
4 103 2006-12-24 19:51:05
5 104 2006-12-25 09:11:52 我要得到的查询结果如下,多一个endtime字段,值为下一个状态(状态为有序的)starttime值:
listrecid state starttime endtime
1 100 2006-12-24 16:45:28 2006-12-24 17:04:28
2 101 2006-12-24 17:04:28 2006-12-24 18:45:28
3 102 2006-12-24 18:45:28 2006-12-24 19:51:05
4 103 2006-12-24 19:51:05 2006-12-25 09:11:52
5 104 2006-12-25 09:11:52 null
LEAD(starttime , 1) OVER (ORDER BY listrecid) AS "endtime"
FROM statelist;
SELECT listrecid , state ,starttime,
LEAD(starttime , 1) OVER (ORDER BY listrecid) AS "endtime"
FROM statelist;
(
select '1' as listrecid, '100'as state, '2006-12-24 16:45:28' as starttime from dual union all
select '2' as listrecid, '101'as state, '2006-12-24 17:04:28' as starttime from dual union all
select '3' as listrecid, '102'as state, '2006-12-24 18:45:28' as starttime from dual union all
select '4' as listrecid, '103'as state, '2006-12-24 19:51:05' as starttime from dual union all
select '5' as listrecid, '104'as state, '2006-12-25 09:11:52' as starttime from dual
)