oracle数据库
表机构如下：
can_use c_date des
1 2012-1-14 0:10:00 繁忙
1 2012-1-14 0:20:00 繁忙
1 2012-1-14 0:30:00 繁忙
1 2012-1-14 0:40:00 繁忙
1 2012-1-14 0:50:00 繁忙
1 2012-1-14 1:00:00 繁忙
0 2012-1-14 1:10:00 空闲
0 2012-1-14 1:20:00 空闲
0 2012-1-14 1:30:00 空闲
0 2012-1-14 1:40:00 空闲
0 2012-1-14 1:50:00 空闲
0 2012-1-14 2:00:00 空闲
需要分段找出can_use为0的时间“段”。
数据可以有十万条，can_use中的0和1可能交替出现N遍，结果应该得出N条记录，
如上活得的结果是
can_use star end des
0 2012-1-14 1:10:00 2012-1-14 2:00:00 空闲
当然，能将1、0按照时间先后顺序交替列出来更好了。。
谢谢啦。
------解决方案--------------------
--can_use c_date des
with t as (
select 1 as can_use, '2012-1-14 0:10:00' as c_date, '繁忙' as des from dual
union all
select 1, '2012-1-14 0:20:00', '繁忙' from dual
union all
select 1, '2012-1-14 0:30:00', '繁忙' from dual
union all
select 1, '2012-1-14 0:40:00', '繁忙' from dual
union all
select 1, '2012-1-14 0:50:00', '繁忙' from dual
union all
select 1, '2012-1-14 1:00:00', '繁忙' from dual
union all
select 0, '2012-1-14 1:10:00', '空闲' from dual
union all
select 0, '2012-1-14 1:20:00', '空闲' from dual
union all
select 0, '2012-1-14 1:30:00', '空闲' from dual
union all
select 0, '2012-1-14 1:40:00', '空闲' from dual
union all
select 0, '2012-1-14 1:50:00', '空闲' from dual
union all
select 0, '2012-1-14 2:00:00', '空闲' from dual
)
select can_use, min(c_date) as fstart, max(c_date) as fend ,des from (
select can_use,to_date(c_date,'yyyy-mm-dd hh24:mi:ss') as c_date,des from t where can_use = 0
) group by can_use,des
CAN_USE FSTART FEND DES
---------------------- ------------------------- ------------------------- ------
0 2012-01-14 01:10:00 2012-01-14 02:00:00 空闲
------解决方案--------------------
with t as (
select 1 as can_use, to_date('2012-01-14 0:10:00', 'yyyy-MM-dd HH24:mi:ss') as c_date , '繁忙' as des from dual union all
select 1 as can_use, to_date('2012-01-14 0:20:00', 'yyyy-MM-dd HH24:mi:ss') as c_date , '繁忙' as des from dual union all
select 1 as can_use, to_date('2012-01-14 0:30:00', 'yyyy-MM-dd HH24:mi:ss') as c_date , '繁忙' as des from dual union all
select 1 as can_use, to_date('2012-01-14 0:40:00', 'yyyy-MM-dd HH24:mi:ss') as c_date , '繁忙' as des from dual union all
select 1 as can_use, to_date('2012-01-14 0:50:00', 'yyyy-MM-dd HH24:mi:ss') as c_date , '繁忙' as des from dual union all
select 1 as can_use, to_date('2012-01-14 1:00:00', 'yyyy-MM-dd HH24:mi:ss') as c_date , '繁忙' as des from dual union all
select 0 as can_use, to_date('2012-01-14 1:10:00', 'yyyy-MM-dd HH24:mi:ss') as c_date , '空闲' as des from dual union all
select 0 as can_use, to_date('2012-01-14 1:20:00', 'yyyy-MM-dd HH24:mi:ss') as c_date , '空闲' as des from dual union all
select 0 as can_use, to_date('2012-01-14 1:30:00', 'yyyy-MM-dd HH24:mi:ss') as c_date , '空闲' as des from dual union all
select 0 as can_use, to_date('2012-01-14 1:40:00', 'yyyy-MM-dd HH24:mi:ss') as c_date , '空闲' as des from dual union all
select 0 as can_use, to_date('2012-01-14 1:50:00', 'yyyy-MM-dd HH24:mi:ss') as c_date , '空闲' as des from dual union all
select 0 as can_use, to_date('2012-01-14 2:00:00', 'yyyy-MM-dd HH24:mi:ss') as c_date , '空闲' as des from dual union all
select 1 as can_use, to_date('2012-01-14 2:10:00', 'yyyy-MM-dd HH24:mi:ss') as c_date , '繁忙' as des from dual
)
select mod(t2.seq,2) can_use,min(t2.c_date),max(t2.c_date),decode(mod(t2.seq,2),0,'空闲','繁忙') des from (