按时间分组查询

--按时间分组查询，可以任意分组，比方123，456，789，10.....分为按小时为4组123一组，456一组，789一组，大于10小时为一组

下面例子为7......18一组，19........6一组跨天计算到第二天六点

select

max(stcd)stcd,sum(id),
to_char(min(tm),'yyyy-mm-dd hh24') tm1 ,to_char(max(tm),'yyyy-mm-dd hh24') tm2
from (select STCD,
      tm,
       id

  from people
 where stcd = '81320460'
   and tm > to_date('2009-07-05 06', 'yyyy-mm-dd hh24')
   and tm <= to_date('2009-07-08 05', 'yyyy-mm-dd hh24')
   )
  group   by
  decode(sign(to_char(tm,'hh24')-6),-1, to_char(tm-1,'yyyymmdd'),0, to_char(tm-1,'yyyymmdd'),1,to_char(tm,'yyyymmdd'))
  ||decode((to_char(tm,'hh24')-0),0,'18',1,'18',2,'18',3,'18',4,'18',5,'18',6,'18',7,'06',8,'06',9,'06',10,'06',11,'06',12,'06',13,'06',14,'06',15,'06',16,'06',17,'06',18,'06',19,'18',20,'18',21,'18',22,'18',23,'18')
   order by stcd,tm1;