按时间分组查询

最新推荐文章于 2024-10-21 07:47:10 发布

原创最新推荐文章于 2024-10-21 07:47:10 发布 · 535 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#date

数据库操作专栏收录该内容

11 篇文章

订阅专栏

--按时间分组查询，可以任意分组，比方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;