如题:
统计表中昨天每隔10分钟的记录数。
时间段中有就显示具体count数,没有就显示0!create table tb(id number, dtime date)insert into tb (id, dtime)
values (1, to_date('2010-05-26 15:17:06', 'dd-mm-yyyy hh24:mi:ss'))
/
insert into tb (id, dtime)
values (2, to_date('2010-05-26 15:23:06', 'dd-mm-yyyy hh24:mi:ss'))
/
insert into tb (id, dtime)
values (3, to_date('2010-05-26 15:25:08', 'dd-mm-yyyy hh24:mi:ss'))
/
insert into tb (id, dtime)
values (4, to_date('2010-05-26 16:23:06', 'dd-mm-yyyy hh24:mi:ss'))
/
insert into tb (id, dtime)
values (5, to_date('2010-05-26 16:51:44', 'dd-mm-yyyy hh24:mi:ss'))
/
insert into tb (id, dtime)
values (6, to_date('2010-05-26 17:02:44', 'dd-mm-yyyy hh24:mi:ss'))
/
insert into tb (id, dtime)
values (7, to_date('2010-05-26 17:11:30', 'dd-mm-yyyy hh24:mi:ss'))
/
insert into tb (id, dtime)
values (8, to_date('2010-05-26 18:14:44', 'dd-mm-yyyy hh24:mi:ss'))
/
insert into tb (id, dtime)
values (9, to_date('2010-05-26 19:11:30', 'dd-mm-yyyy hh24:mi:ss'))
/
insert into tb (id, dtime)
values (10, to_date('2010-05-26 19:14:44', 'dd-mm-yyyy hh24:mi:ss'))
/ 希望大家踊跃解答!谢谢!
统计表中昨天每隔10分钟的记录数。
时间段中有就显示具体count数,没有就显示0!create table tb(id number, dtime date)insert into tb (id, dtime)
values (1, to_date('2010-05-26 15:17:06', 'dd-mm-yyyy hh24:mi:ss'))
/
insert into tb (id, dtime)
values (2, to_date('2010-05-26 15:23:06', 'dd-mm-yyyy hh24:mi:ss'))
/
insert into tb (id, dtime)
values (3, to_date('2010-05-26 15:25:08', 'dd-mm-yyyy hh24:mi:ss'))
/
insert into tb (id, dtime)
values (4, to_date('2010-05-26 16:23:06', 'dd-mm-yyyy hh24:mi:ss'))
/
insert into tb (id, dtime)
values (5, to_date('2010-05-26 16:51:44', 'dd-mm-yyyy hh24:mi:ss'))
/
insert into tb (id, dtime)
values (6, to_date('2010-05-26 17:02:44', 'dd-mm-yyyy hh24:mi:ss'))
/
insert into tb (id, dtime)
values (7, to_date('2010-05-26 17:11:30', 'dd-mm-yyyy hh24:mi:ss'))
/
insert into tb (id, dtime)
values (8, to_date('2010-05-26 18:14:44', 'dd-mm-yyyy hh24:mi:ss'))
/
insert into tb (id, dtime)
values (9, to_date('2010-05-26 19:11:30', 'dd-mm-yyyy hh24:mi:ss'))
/
insert into tb (id, dtime)
values (10, to_date('2010-05-26 19:14:44', 'dd-mm-yyyy hh24:mi:ss'))
/ 希望大家踊跃解答!谢谢!
max(substr(to_char(t.dtime, 'hh24:mi'), 0, 4)) || '9',
sum(id)
from tb t
group by substr(to_char(t.dtime, 'hh24mi'), 0, 3)
order by max(to_char(t.dtime, 'hh24mi'))
with tmp as
(
select
trunc(sysdate) - 1 + (interval '1' minute) * 10 * (level - 1) begin_time,
trunc(sysdate) - 1 + (interval '1' minute) * 10 * (level) end_time
from dual
connect by level <= 24 * 60 / 10
)
select tmp.begin_time,tmp.end_time,count(tb.id)
from tmp,tb
where tb.dtime(+) >= tmp.begin_time and tb.dtime(+) < tmp.end_time
group by tmp.begin_time,tmp.end_time;
from tb
group by substr(to_char(dtime,'yyyy-mm-dd hh24:mi'),1,14)||'0', count(*);
from tb
group by substr(to_char(dtime,'yyyy-mm-dd hh24:mi'),1,14)||'0';