一些数据示例:totalactpower rectime5 2011-07-20 00:09:00
5 2011-07-20 00:10:00
4.2 2011-07-21 00:18:00
2.3 2011-07-22 00:20:00
1.4 2011-07-23 00:25:00
select sum(totalactpower) totalactpower,to_char(rectime,'hh24:mi:ss') as rectime from table1 group by to_char(rectime,'hh24:mi:ss')11.1 00:09:00
5.8 00:10:00
22.2 00:18:00
33.3 00:20:00
44.4 00:25:00
我想得到的结果
11.1 00:09:00 2011-07-20 00:09:00
5.8 00:10:00 2011-07-20 00:10:00
22.2 00:18:00 2011-07-21 00:18:00
33.3 00:20:00 2011-07-22 00:20:00
44.4 00:25:00 2011-07-23 00:25:00
现在是按时间分组 取和的 我想在时间分组的基础上 得到求和 和 日期+时间 而不是 求和 时间给个思路 谢谢!
5 2011-07-20 00:10:00
4.2 2011-07-21 00:18:00
2.3 2011-07-22 00:20:00
1.4 2011-07-23 00:25:00
select sum(totalactpower) totalactpower,to_char(rectime,'hh24:mi:ss') as rectime from table1 group by to_char(rectime,'hh24:mi:ss')11.1 00:09:00
5.8 00:10:00
22.2 00:18:00
33.3 00:20:00
44.4 00:25:00
我想得到的结果
11.1 00:09:00 2011-07-20 00:09:00
5.8 00:10:00 2011-07-20 00:10:00
22.2 00:18:00 2011-07-21 00:18:00
33.3 00:20:00 2011-07-22 00:20:00
44.4 00:25:00 2011-07-23 00:25:00
现在是按时间分组 取和的 我想在时间分组的基础上 得到求和 和 日期+时间 而不是 求和 时间给个思路 谢谢!
SELECT SUM(TOTALACTPOWER)OVER(PARTITION BY TO_CHAR(RECTIME, 'hh24:mi:ss')) TOTALACTPOWER,
TO_CHAR(RECTIME, 'hh24:mi:ss') AS RECTIME,
RECTIME
FROM TABLE1;
from (SELECT SUM(TOTALACTPOWER) OVER(PARTITION BY TO_CHAR(RECTIME, 'hh24:mi:ss')) TOTALACTPOWER,
TO_CHAR(RECTIME, 'hh24:mi:ss') AS RECTIME_CHILD,
row_number() OVER(PARTITION BY TO_CHAR(RECTIME, 'hh24:mi:ss') order by TOTALACTPOWER asc) as cn,
RECTIME
FROM TABLE1)
where cn = 1;
如果想别人给你全写出来的话,请把你数据的计算方式项目描述一下.