sql 按时间查询解决方案
(1)SQL按时间查询
以前没太注意SQL语句中按时间条件查询,结果写了个SQL查询报错
select d.*,d.rowid from dy_date d where d.work_group_id = '05807e481fd0f1c0011ffde5ad457be4' and d.duty_date <= to_date('2009-03-17')
order by d.duty_date, d.start_time;
应该注意将字符串转为日期的函数应该有一个日期格式参数
所以修改后的SQL语句应该如下所示:
select d.*,d.rowid from dy_date d where d.work_group_id = '05807e481fd0f1c0011ffde5ad457be4' and d.duty_date <= to_date('2009-03-17', 'yyyy-mm-dd')
order by d.duty_date, d.start_time;
(2)SQL按时间段查询
如表1:
2007-6-27 10:00 20
2007-6-27 11:00 60
2007-6-27 11:25 15
2007-6-27 12:05 100
2007-6-27 12:59 10
2007-6-27 13:00 150
出來的效果是要
2007-6-27 10:00-10:59 20
2007-6-27 11:00-11:59 75
2007-6-27 12:00-12:59 110
2007-6-27 13:00-13:59 150
在oracle中可以这样实现
create table t(cdate date,cnum int);
/
insert into t
select to_date( '2007-6-27 10:00 ', 'yyyy-mm-dd HH24:MI '),20 from dual union all
select to_date( '2007-6-27 11:00 ', 'yyyy-mm-dd HH24:MI '),60 from dual union all
select to_date( '2007-6-27 11:25 ', 'yyyy-mm-dd HH24:MI '),15 from dual union all
select to_date( '2007-6-27 12:05 ', 'yyyy-mm-dd HH24:MI '),100 from dual union all
select to_date( '2007-6-27 12:59 ', 'yyyy-mm-dd HH24:MI '),10 from dual union all
select to_date( '2007-6-27 13:00 ', 'yyyy-mm-dd HH24:MI '),150 from dual;
/
select to_char( cdate, 'yyyy-mm-dd ' )|| ' '||to_char( cdate, 'HH24 ' )|| ':00- '||to_char( cdate, 'HH24 ' )|| ':59 ', sum(cnum) from t group by to_char( cdate, 'yyyy-mm-dd ' ),to_char( cdate, 'HH24 ' )
--执行结果
2007-06-27 10:00-10:59 20
2007-06-27 11:00-11:59 75
2007-06-27 12:00-12:59 110
2007-06-27 13:00-13:59 150