17,086
社区成员
发帖
与我相关
我的任务
分享
--创建测试表
--循环插入测试数据
declare n number;
begin
n:=0;
while n<50000 loop
n :=n+1;
insert into hour_test
select * from (
select rownum rn,to_date('2017-01-01', 'yyyy-mm-dd hh:mi:ss')+ rownum/24 add_hour from dual
connect by rownum<25) a
where a.rn =(select round(dbms_random.value(1,24)) from dual);
end loop;
commit;
end ;
--查询结果
select to_char(add_hour,'hh24') hour ,count(*) n from hour_test
group by to_char(add_hour,'hh24')
order by to_char(add_hour,'hh24');
create table orderList(
createTime date default(sysdate),
name varchar2(20)
)
----------------------------------------
insert into orderList (name) values ('aa');
insert into orderList (name) values ('bb');
insert into orderList (name) values ('cc');
insert into orderList (name) values ('dd');
insert into orderList (name) values ('ee');
insert into orderList (name) values ('ff');
----------------------------------------
with C_tab as(
select to_char(sysdate,'yyyy-mm-dd')||' '||lpad(level,2,'0') atime from dual connect by level <=24)
select b.atime,count(nvl(a.createtime,null)) from orderList a right join C_tab b on to_char(a.createtime,'yyyy-mm-dd hh24') = b.atime
group by b.atime
----------------------------------------
drop table orderList;
新年,我的写法。