需求:给定三个参数,1是统计开始时间(比如2009-09-09 09:09:00),2是统计结束时间(比如2009-09-10 10:10:00),3是时间间隔,比如10分钟,希望把表A内介于开始时间和结束时间之间,每10分钟内的插入的记录条数统计出来,然后返回。我现在的办法是在程序里写个循环,每次查询后放到数组里,可是这样效率太低了,请问能不能用一次查询实现?多谢!
调试欢乐多
from tt
where datetime<=endtime
and datetime>=starttime
group by trunc((datetime-starttime)/10*60*24)
参考下
from tt
where datetime <=endtime
and datetime>=starttime
group by trunc((datetime-starttime)/intervaltime*60*24)
from tt
where datetime <=:endtime
and datetime>=:starttime
group by trunc((datetime-:starttime)/:intervaltime*60*24)
应该ok了
嘿嘿
--参考 模为4,开始为1,结束为9
with tt as
(
select 1 as id 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 9 from dual)select ceil(id/4) ,count(1)
from tt
where id>=1 and id<=9
group by ceil(id/4)