日期:2014-05-17  浏览次数:21347 次

关于Oracle的时间分段查询
目前手上在做一个水表的监控系统,数据库是Oracle,有一个功能是按照时间段做报表。
现在有一个流量表FlowData,字段有id(编号),msgTime(时间),nFlow(流量值),想在某一天内统计各个时段(1小时、2小时、4小时等等,都能被24整除,以2小时为例)的流量,查询效果如下:
时段 流量
0:00-2:00 XX
2:00-4:00 XX
4:00-6:00 XX
.
.
22:00-0:00(也可以是24:00) XX
如果说有的时段没有值,也得查询出来(0也可以不显示)如:
2:00-4:00 0
谢谢大家~~

------解决方案--------------------
语句:
SQL code

SELECT '00:00-02:00' AS h, SUM(nFlow) AS s
FROM flowdata
WHERE msgtime BETWEEN to_date('2011-12-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND to_date('2011-12-01 02:00:00', 'YYYY-MM-DD HH24:MI:SS')

UNION ALL

SELECT '02:00-04:00' AS h, SUM(nFlow) AS s
FROM flowdata
WHERE msgtime BETWEEN to_date('2011-12-01 02:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND to_date('2011-12-01 04:00:00', 'YYYY-MM-DD HH24:MI:SS')

UNION  ALL 
SELECT '04:00-06:00' AS h, SUM(nFlow) AS s
FROM flowdata
WHERE msgtime BETWEEN to_date('2011-12-01 04:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND to_date('2011-12-01 06:00:00', 'YYYY-MM-DD HH24:MI:SS')

UNION ALL
SELECT '06:00-08:00' AS h, SUM(nFlow) AS s
FROM flowdata
WHERE msgtime BETWEEN to_date('2011-12-01 06:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND to_date('2011-12-01 08:00:00', 'YYYY-MM-DD HH24:MI:SS')

UNION ALL
SELECT '08:00-10:00' AS h, SUM(nFlow) AS s
FROM flowdata
WHERE msgtime BETWEEN to_date('2011-12-01 08:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND to_date('2011-12-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS')

UNION  ALL
SELECT '10:00-12:00' AS h, SUM(nFlow) AS s
FROM flowdata
WHERE msgtime BETWEEN to_date('2011-12-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND to_date('2011-12-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS')

------解决方案--------------------
楼主可以将5楼的内容写到存储过程里呀,通过循环去组装这些语句呀!
------解决方案--------------------
如果是查询一个月显示每天数量到是有简单方法 查小时应该只有查询每个时段拼接了