目前手上在做一个水表的监控系统,数据库是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
谢谢大家~~
现在有一个流量表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
谢谢大家~~
(
id NUMBER not null,
msgtime DATE,
nflow NUMBER
)insert into FLOWDATA (ID, MSGTIME, NFLOW)
values (1, to_date('01-12-2012 01:12:01', 'dd-mm-yyyy hh24:mi:ss'), 1);insert into FLOWDATA (ID, MSGTIME, NFLOW)
values (2, to_date('01-12-2012 01:15:01', 'dd-mm-yyyy hh24:mi:ss'), 2);insert into FLOWDATA (ID, MSGTIME, NFLOW)
values (3, to_date('01-12-2011 03:15:01', 'dd-mm-yyyy hh24:mi:ss'), 1);insert into FLOWDATA (ID, MSGTIME, NFLOW)
values (4, to_date('01-12-2011 06:01:01', 'dd-mm-yyyy hh24:mi:ss'), 2);insert into FLOWDATA (ID, MSGTIME, NFLOW)
values (5, to_date('01-12-2011 08:06:01', 'dd-mm-yyyy hh24:mi:ss'), 1);insert into FLOWDATA (ID, MSGTIME, NFLOW)
values (6, to_date('01-12-2011 09:30:01', 'dd-mm-yyyy hh24:mi:ss'), 2);insert into FLOWDATA (ID, MSGTIME, NFLOW)
values (7, to_date('01-12-2011 10:20:01', 'dd-mm-yyyy hh24:mi:ss'), 2);insert into FLOWDATA (ID, MSGTIME, NFLOW)
values (8, to_date('01-12-2011 11:02:01', 'dd-mm-yyyy hh24:mi:ss'), 1);
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 ALLSELECT '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')
结果:
因为测试数据只到12点的,如果再增加其它时间段,就再增加几个union all子句。