表名: Flow 列: amount int(5), statictime timestamp要求: 查询指定时间内的amount之和,每隔五条记录统计一下, 时间顺序不是严格按顺序的,所以要先排序
如记录为:
amount statictime
1 201207191032
2 201207191033
3 201207191034
4 201207191035
5 201207191036
11 201207191037
7 201207191038
8 201207191039
9 201207191040
10 201207191041
要求查询201207191032到201207191041的统计结果为2条记录的sql语言怎么写:15 // 1+2+3+4+5
45 // 11+7+8+9+10
如记录为:
amount statictime
1 201207191032
2 201207191033
3 201207191034
4 201207191035
5 201207191036
11 201207191037
7 201207191038
8 201207191039
9 201207191040
10 201207191041
要求查询201207191032到201207191041的统计结果为2条记录的sql语言怎么写:15 // 1+2+3+4+5
45 // 11+7+8+9+10
as
(select 1 a from dual
union all
select 2 from dual
union all
select 3 from dual
union all
select 4 from dual
union all
select 5 from dual
union all
select 6 from dual
union all
select 7 from dual
union all
select 8 from dual
union all
select 12 from dual
union all
select 111 from dual
)
select sum(a)
from
(
select a,case when rownum>5 then 'A' else 'B' end b from tt
) group by b--result:15 /// 1+2+3+4+5
144 //// 6+7+8+12+111
from (select rownum as num,f.amount
from flow f
order by f.statictime) t
group by round((t.num / 5 + 0.4), 0);
with tt
as
(select 1 a, 201207191032 time from dual
union all
select 2,201207191033 from dual
union all
select 3,201207191035 from dual
union all
select 4 ,201207191038 from dual
union all
select 5 ,201207191039 from dual
union all
select 6 ,201207191041 from dual
union all
select 7 ,201207191042 from dual
union all
select 8 ,201207191043 from dual
union all
select 10 ,201207191046 from dual
union all
select 11,201207191047 from dual
union all
select 16 ,201207191052 from dual
union all
select 7 ,201207191053 from dual
union all
select 8 ,201207191054 from dual
union all
select 12 ,201207191058 from dual
union all
select 4,201207191059 from dual
union all
select 401,201207191159 from dual
)
select sum(t3.a),t3.b
from
(
select t2.*, round((t2.rn-3)/5, 0) b from (
select t1.*, rownum rn from tt t1 order by time) t2
) t3 group by t3.b
order by t3.b
测试了 应该是没问题的 数据多是可以的。2楼的只可以处理行数据