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;新年,我的写法。
--创建测试表 --循环插入测试数据 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');
表名
orderList 字段
createTime name .......
2014-02-14 12:11:22 test
不好意思可能是我没说清楚,你这样是查询所有的数据 我只要查一天之类的数据, 并且把为空的 也查询出来 比如2点 0 3点0.你这样查询 数据为空的 查询不出来 谢谢
要查一天的就加上where条件,至于数据为空的也要查出来的话可以考虑建一个表,存00~23这些值,左连接一下
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;新年,我的写法。
--循环插入测试数据
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');
我的解决办法