多个逻辑处理，一个SQL能搞定吗？求助

njsteve 2013-05-03 09:10:17

START_CI name_id start_time stay_time(分钟)
11933 test01 2013-1-7 11:01 779
38165 test02 2013-1-7 8:36 284
3244 test02 2013-1-7 13:20 316
38165 test02 2013-1-7 18:36 303
3210 test02 2013-1-7 23:39 21
一天划分的时间段为：
休息时段:1 0:00~08:00 共8小时
工作时段1: 08:00~12:00 共4小时
午休时段: 12:00~14:00 共2小时
工作时段2: 14:00~18:00 共4小时
休闲时段: 18:00~23:00 共5小时
休息时段2: 23:00~00:00 共1小时
现在统计每个用户在不同的时间段驻留时长。
期望结果：
用户test01
工作时段1: 08:00~12:00 59分钟( 在11933处) 11933
午休时段: 12:00~14:00 2小时( 在11933处) 11933
工作时段2: 14:00~18:00 4小时( 在11933处) 11933
休闲时段: 18:00~23:00 5小时( 在11933处) 11933
休息时段: 23:00~00:00 1小时( 在11933处） 11933

用户 test02
休息时段1: 0:00~08:00 （说明：最开始时间是：2013-1-7 8:36:00 故可不算，删除这个时间段）
工作时段1: 08:00~12:00 (12：00-8:36=3小时24分(小区 38165)) 38165 test02
午休时段: 12:00~14:00 (284-3：24=80分钟(小区 38165)（这条记录是拆分的，由于TEST02用户在38165驻留时长284分钟，在8：00~12:00区间驻留时长为3:24分钟，还剩余的时长，放到休息时间段中）) 38165 test02
午休时段: 12:00~14:00 (40分钟(小区 3244)) 3244 test02
工作时段2: 14:00~18:00 (316-40=276分钟(小区 3244 也是拆分的记录)) 3244 test02
休闲时段: 18:00~23:00 36分(小区 3244 此条记录也是拆分的记录)) 3244 test02
休闲时段: 18:00~23:00 4小时24分(小区 38165) 38165 test02
休息时段2: 23:00~00:00 39分(小区 38165) 38165 test02
休息时段2: 23:00~00:00 21分(小区 3210) 3210 test02

测试脚本脚本为：
create table TEST
(
START_CI NUMBER(32),
name_id VARCHAR2(25),
TIME_STAMP DATE,
stay_time number(10)
);
insert into TEST (START_CI,name_id,TIME_STAMP) values(11933,'test01',to_date('2013010711:01:00','yyyymmdd hh24:mi:ss'),779);
insert into TEST (START_CI,name_id,TIME_STAMP) values(38165,'test02', to_date('2013010708:36:00','yyyymmdd hh24:mi:ss'),284);
insert into TEST (START_CI,name_id,TIME_STAMP) values(3244,'test02', to_date('2013010713:20:00','yyyymmdd hh24:mi:ss'),316);
insert into TEST (START_CI,name_id,TIME_STAMP) values(38165,'test02', to_date('2013010718:36:00','yyyymmdd hh24:mi:ss'),303);
insert into TEST (START_CI,name_id,TIME_STAMP) values(3210,'test02', to_date('20130107 23:39:00','yyyymmdd hh24:mi:ss'),21);

...全文