oracle 按时间段分类查询

[gdfg34324]

有一个表ta
字段a
字段b
a b
2 09:12:00
5 09:18:36

现在要求对按时间段每隔一小时对a字段进行求和？

[gdfg34324]

达人出现吧

[BenChiM888]

select trunc(b,'hh24') as logdate,sum(a)
  from ta
 group by trunc(b,'hh24');

[tangren]

SELECT t.st, SUM(ta.a)
  FROM ta,
       (SELECT lpad(LEVEL - 1, 2, '0') st, lpad(LEVEL, 2, '0') et
          FROM dual
        CONNECT BY LEVEL <= 24) t
 WHERE ta.b >= t.st
   AND ta.b < t.et
 GROUP BY t.st;

[tangren]

with ta as (
  select 2 a, '09:12:00' b from dual union all
  select 5 a, '09:18:36' b from dual)
SELECT t.st, nvl(SUM(a),0) total_a
  FROM ta,
       (SELECT lpad(LEVEL - 1, 2, '0') st, lpad(LEVEL, 2, '0') et
          FROM dual
        CONNECT BY LEVEL <= 24) t
 WHERE ta.b(+) >= t.st
   AND ta.b(+) < t.et
 GROUP BY t.st
 order by st;