我要去昨天全天的数据,这个sql日期判断条件如何写啊 select * from t_custmerwhere trunc(sysdate-post_time)>0这个sql只能取一部分,取不到00:00:00到23:59:59之间的数据。问下这种sql如何写啊? 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 select * from t_custmer where trunc(sysdate-post_time) = 1 trunc不行,取不到23:20:20分的数据。 trunc(sysdate)-trunc(post_time) = 1 select *from t_custmerwhere trunc(sysdate-1)=trunc(post_time) select * from t_custmer where post_time>=trunc(sysdate-1) and post_time<trunc(sysdate)数据量大且post_time上有索引的话这么写能避免全表扫描 因为狂狼这个写法中的函数都是加在sysdate上的 并没有加在列post_time上你前面的写法trunc(post_time)这样可能不会走索引不过可以加个函数索引 呵呵个人见解哦 select * from t_custmer where to_char(sysdate-1,'YYYYMMDD')=to_char(post_time,'YYYYMMDD') http://school.cnd8.com/oracle/jiaocheng/37343.htm我认为在这里建函数索引不是个明智的选择,只要稍微换种写法,不要在条件post_time上加函数即可我这里有500万的测试数据select count(1) from largetest where trunc(birthday,'hh')=to_date('1984111111','yyyymmddhh24');/*执行计划 EXECUTION_PLAN0 SELECT STATEMENT Cost = 89951 SORT AGGREGATE 2 PARTITION RANGE ALL Cost = 13 TABLE ACCESS FULL LARGETEST TABLE Cost = 1*/select count(1) from largetest where birthday>=to_date('1984111111','yyyymmddhh24') and birthday<to_date('1984111112','yyyymmddhh24');/*LGTEST_BIRTHDAY 为birthday列的索引EXECUTION_PLAN0 SELECT STATEMENT Cost = 31 SORT AGGREGATE 2 PARTITION RANGE SINGLE Cost = 13 INDEX RANGE SCAN LGTEST_BIRTHDAY INDEX Cost = 1*/ 安装oracle时,出现了oracle universal installer怎么回事 两个比较简单的oracle问题 新手求教:关于oracle SQL PLUS 与数据库的问题 oracle insert 返回值 请教一个存储过程的问题 组合算法,请求帮助???? 倒入导出问题? 同表分组,sql求助 etang 如何解决在oracle中的转意字符问题 pl/sql 模糊查询的SQL语句
trunc不行,取不到23:20:20分的数据。
from t_custmer
where trunc(sysdate-1)=trunc(post_time)
where post_time>=trunc(sysdate-1) and post_time<trunc(sysdate)数据量大且post_time上有索引的话这么写能避免全表扫描
你前面的写法trunc(post_time)这样可能不会走索引不过可以加个函数索引 呵呵个人见解哦
select * from t_custmer
where to_char(sysdate-1,'YYYYMMDD')=to_char(post_time,'YYYYMMDD')
select count(1) from largetest where trunc(birthday,'hh')=to_date('1984111111','yyyymmddhh24');/*执行计划
EXECUTION_PLAN
0 SELECT STATEMENT Cost = 8995
1 SORT AGGREGATE
2 PARTITION RANGE ALL Cost = 1
3 TABLE ACCESS FULL LARGETEST TABLE Cost = 1
*/
select count(1) from largetest where birthday>=to_date('1984111111','yyyymmddhh24') and birthday<to_date('1984111112','yyyymmddhh24');
/*LGTEST_BIRTHDAY 为birthday列的索引
EXECUTION_PLAN
0 SELECT STATEMENT Cost = 3
1 SORT AGGREGATE
2 PARTITION RANGE SINGLE Cost = 1
3 INDEX RANGE SCAN LGTEST_BIRTHDAY INDEX Cost = 1
*/