已经做了useday列索引
1、---------------用TO_date函数查询速度很快-----------------------
select a.goodsid,b.vipid from pub_goods a,resa_sa_dtl b where a.goodsid=b.goodsid and b.vipid>0
and b.vipid not in(3344,1674,9028) and a.goodsname like '%(特%'
and b.useday between
TO_DATE('2012-03-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND TO_DATE('2012-03-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss')2、-------------用TO_char函数查询速度好慢-------------------------------------select goods from pub_goods a,resa_sa_dtl b where a.goodsid=b.goodsid and b.vipid>0
and b.vipid not in(3344,1674,9028) and to_char(b.useday,'yyyy-mm-dd')=
to_char(sysdate,'yyyy-mm-dd')
and a.goodsname like '%(特%' 我想查询条件中b.useday等于今天的日期就行了(sysdate代入1中的TO_date函数)。
1、---------------用TO_date函数查询速度很快-----------------------
select a.goodsid,b.vipid from pub_goods a,resa_sa_dtl b where a.goodsid=b.goodsid and b.vipid>0
and b.vipid not in(3344,1674,9028) and a.goodsname like '%(特%'
and b.useday between
TO_DATE('2012-03-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND TO_DATE('2012-03-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss')2、-------------用TO_char函数查询速度好慢-------------------------------------select goods from pub_goods a,resa_sa_dtl b where a.goodsid=b.goodsid and b.vipid>0
and b.vipid not in(3344,1674,9028) and to_char(b.useday,'yyyy-mm-dd')=
to_char(sysdate,'yyyy-mm-dd')
and a.goodsname like '%(特%' 我想查询条件中b.useday等于今天的日期就行了(sysdate代入1中的TO_date函数)。
解决方案 »
- oracle11g dbconsoleorl启动问题
- 救命啊,已经调用了DAO里面的session.update()方法,但是数据库里面的数据就是不更新,求高手帮忙
- 超挑战ORACLE/SQL请教
- 请教,这种情况下oracle的数据怎么恢复?
- 这两条语句哪个更好?
- 紧急在线求助:如何写这样的存储过程?
- 为什么我在操作hr表空间中的数据时候一定要添上表空间名字?
- C盆空间不够了,如何改变ORACLE的默认存储空间。从.dmp文件还原一个数据库如何指定它的存储目录
- 求一句sql!!!急!!!
- 初学者问题:欢迎各位前辈指教
- 快来帮我,感谢,关于ORACLE sql developer乱码问题!
- ORA-00979: 不是 GROUP BY 表达式 急求帮助
不能用to_char,太慢了。
select a.goodsid, b.vipid
from pub_goods a, resa_sa_dtl b
where a.goodsid = b.goodsid
and b.vipid > 0
and b.vipid not in (3344, 1674, 9028)
and a.goodsname like '%(特%'
and b.useday >trunc(sysdate,'dd')
select a.goodsid, b.vipid
from pub_goods a, resa_sa_dtl b
where a.goodsid = b.goodsid
and b.vipid > 0
and b.vipid not in (3344, 1674, 9028)
and a.goodsname like '%(特%'
and b.useday >trunc(sysdate,'dd')
FROM ...
WHERE b.useday >= trunc(SYSDATE)
AND b.useday <= trunc(SYSDATE) + 0.99999