问题：查询 2011-01-06 到 2011-01-08这几天中 9:00到15:30的数据。(如果天的跨度是随机的，如隔10天，20天，SQL如何写最优？)

create table T_DATE_QUERY

(

ID NUMBER,

NAME varCHAR2(6),

START_DATE DATE,

CNT NUMBER

);

--从2012-01-05 00:00:00开始，每隔30分钟生成一条数据。

declare

i number;

j number;

begin

for i in 1 .. 5 loop

for j in 1 .. 48 loop

insert into t_date_query

select i,'00'||i, (to_date('2012-01-05 00:00:00','YYYY-MM-DD HH24:MI:SS')+(i-0))-(j*30)/(24*60),trunc(j*dbms_random.value(10,20)) from dual;

end loop;

commit;

end loop;

end;

/

select * from t_date_query order by start_date;

笨办法实现：查询 2011-01-06 到 2011-01-08这几天中 9:00到15:30的数据。（如果时间跨的浮度很大则不好扩展，代码冗余）

select a.*

from t_date_query a

where start_date >= to_date('2012-01-06','YYYY-MM-DD') and start_date

and not (start_date >=to_date('2012-01-06 00:00:00','YYYY-MM-DD hh24:mi:ss') and start_date <=to_date('2012-01-06 08:59:59','YYYY-MM-DD hh24:mi:ss'))

and not (start_date >=to_date('2012-01-06 15:31:00','YYYY-MM-DD hh24:mi:ss') and start_date <=to_date('2012-01-06 23:59:59','YYYY-MM-DD hh24:mi:ss'))

and not (start_date >=to_date('2012-01-07 00:00:00','YYYY-MM-DD hh24:mi:ss') and start_date <=to_date('2012-01-07 08:59:59','YYYY-MM-DD hh24:mi:ss'))

and not (start_date >=to_date('2012-01-07 15:31:00','YYYY-MM-DD hh24:mi:ss') and start_date <=to_date('2012-01-07 23:59:59','YYYY-MM-DD hh24:mi:ss'))

and not (start_date >=to_date('2012-01-08 00:00:00','YYYY-MM-DD hh24:mi:ss') and start_date <=to_date('2012-01-08 08:59:59','YYYY-MM-DD hh24:mi:ss'))

and not (start_date >=to_date('2012-01-08 15:31:00','YYYY-MM-DD hh24:mi:ss') and start_date <=to_date('2012-01-08 23:59:59','YYYY-MM-DD hh24:mi:ss'))

;

--为了验证下面的SQL，添加一条 06-01-2012 15:31:00 的数据

insert into t_date_query (ID, NAME, START_DATE, CNT)

values (2, '002 ', to_date('06-01-2012 15:31:00', 'dd-mm-yyyy hh24:mi:ss'), 1);

delete from t_date_query

where start_date=to_date('06-01-2012 15:31:00', 'dd-mm-yyyy hh24:mi:ss');

--通过先控制YYYY-MM-DD 再控制 hh24:mi:ss的方法实现：（目前想到最好的实现方法）

select a.*

from t_date_query a

where start_date >= to_date('2012-01-06','YYYY-MM-DD') and start_date

and to_number(substr(to_char(start_date,'YYYY-MM-DD hh24:mi:ss'),12,2)||substr(to_char(start_date,'YYYY-MM-DD hh24:mi:ss'),15,2)) between 900 and 1530

order by start_date

;

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/11990065/viewspace-722096/ ，如需转载，请注明出处，否则将追究法律责任。

ITPub博客

SQL查询 2011-01-06 到 2011-01-08这几天中 9:00到15:30的数据

最新文章