问题：oracle中插入分割时间的sql语句

问题：

我的查询一段时间内的每个状态的时间。（每天以7点半作为分割点）
数据库： oracle
表名：table
字段： 状态：status
      时间： mydate
比如我要查询2008-12-01到2008-12-09之间的数据：

 

select status,mydate from table where mydate-7.5/24 > to_date('2008/12/01','YYYY/MM/DD') 
 and mydate-7.5/24 < to_date('2008/12/09','YYYY/MM/DD') order by mydate;

 
即附件中得到的原始数据。然后要在每一天的分割点插入分割时间，状态用分割点的上一条记录的状态（即附件中插入分割时间下面的数据），
右边数据为我要得到的结果。是相邻每条数据的时间差，状态用被减数的状态。
我想用一条sql得到右边的结果，谢谢。

 

解答：

 

思路：
把相邻的两条数据组合成一条数据，然后组装时间段内的所有的分割点的记录。
再group by day,status来查询。
然后判断是用分割点的时间还是用结束的时间来判断来减开始的时间还是分割点的时间。
比如：查找2008年10月份的数据（具体的时间段可以自己设定）

select
  date_list.s_date,
  mc_stat_h.status,
  sum( decode( sign( mc_stat_h.end_date - date_list.end_date ), -1, mc_stat_h.end_date, date_list.end_date ) - 
    decode( sign( mc_stat_h.start_date - date_list.start_date), -1, date_list.start_date, mc_stat_h.start_date ) ) * 24 sum_time
from
(
  select 
    to_date('200810','yyyymm')+(rownum-1) s_date,
    to_date('200810','yyyymm')+(rownum-1)+7.5/24 start_date,
    to_date('200810','yyyymm')+rownum+7.5/24 end_date 
  from dual
  connect by rownum<=last_day(to_date('200810','yyyymm')) - to_date('200810','yyyymm')+1
) date_list,
(
  select
    pre_stat.status,
    pre_stat.start_date,
    nvl(next_stat.end_date, decode( sign(last_day(to_date('200810','yyyymm')) + 1 + 7.5/24 - sysdate), -1 , last_day(to_date('200810','yyyymm')) + 1 + 7.5/24, sysdate)) end_date
  from 
  (
    select
      rownum id,
      status,
      mydate start_date 
    from table 
    order by status_date
  ) pre_stat,
  (
    select
      rownum id,
      mydate end_date 
    from table 
    order by status_date
  ) next_stat
  where 
    pre_stat.id = next_stat.id(+) - 1
  order by pre_stat.start_date
) mc_stat_h
where ( mc_stat_h.start_date >= date_list.start_date and mc_stat_h.start_date < date_list.end_date )
or ( mc_stat_h.end_date >= date_list.start_date and mc_stat_h.end_date < date_list.end_date )
or ( mc_stat_h.start_date <= date_list.start_date and mc_stat_h.end_date >= date_list.start_date )
or ( mc_stat_h.start_date <= date_list.end_date and mc_stat_h.end_date >= date_list.end_date )
group by date_list.s_date, mc_stat_h.status;