select * from (
select v.*,rownum rnm from (
select distinct(a.CALL_ID),a.OPERATOR_NO,a.OPERATOR_NAME,a.SRC_PHONE_NO,a.QUOTE_PHONE_NO,d.TEL_NAME,d.UNIT_NAME,d.FIRST_CODE || ' ' || d.SECOND_CODE,
e.Dest_Id,e.CALL_LEN,a.QUERY_COUNT,b.FILE_NAME,c.satisfy_degree,to_char(to_date(a.QUOTE_TIME,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss'),
b.is_vip,b.sound_id,rownum rn, a.QUOTE_TIME, a.QUOTE_TYPE, d.parent_tel_id, a.CREATE_TIME, a.CUR_CODE_VALUE
from PNDD_QUOTE_DETAIL a,FULLTIME_TAPE_SOUND b,callin_satisfy c, PNDD_TEL_INFO d, callin_detail e
where a.CALL_ID=b.CALL_ID(+)
and a.CALL_ID=c.CALL_ID(+)
and a.CALL_ID=e.CALL_ID(+)
and a.QUOTE_PHONE_NO = d.TEL_NUMBER(+)
and 0 = e.TYPE(+)
and a.create_time >= to_date('2007-08-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and a.create_time <= to_date('2007-09-26 23:59:59','yyyy-mm-dd hh24:mi:ss')
and (to_date(a.QUOTE_TIME,'yyyy-mm-dd hh24:mi:ss')-a.create_time)*24*3600 >= 0
and (to_date(a.QUOTE_TIME,'yyyy-mm-dd hh24:mi:ss')-a.create_time)*24*3600 <= 99999999
order by a.create_time desc
) v
where rownum <= 40)
where rnm >= 21
我的速度是13’现在还想更快点
select v.*,rownum rnm from (
select distinct(a.CALL_ID),a.OPERATOR_NO,a.OPERATOR_NAME,a.SRC_PHONE_NO,a.QUOTE_PHONE_NO,d.TEL_NAME,d.UNIT_NAME,d.FIRST_CODE || ' ' || d.SECOND_CODE,
e.Dest_Id,e.CALL_LEN,a.QUERY_COUNT,b.FILE_NAME,c.satisfy_degree,to_char(to_date(a.QUOTE_TIME,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss'),
b.is_vip,b.sound_id,rownum rn, a.QUOTE_TIME, a.QUOTE_TYPE, d.parent_tel_id, a.CREATE_TIME, a.CUR_CODE_VALUE
from PNDD_QUOTE_DETAIL a,FULLTIME_TAPE_SOUND b,callin_satisfy c, PNDD_TEL_INFO d, callin_detail e
where a.CALL_ID=b.CALL_ID(+)
and a.CALL_ID=c.CALL_ID(+)
and a.CALL_ID=e.CALL_ID(+)
and a.QUOTE_PHONE_NO = d.TEL_NUMBER(+)
and 0 = e.TYPE(+)
and a.create_time >= to_date('2007-08-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and a.create_time <= to_date('2007-09-26 23:59:59','yyyy-mm-dd hh24:mi:ss')
and (to_date(a.QUOTE_TIME,'yyyy-mm-dd hh24:mi:ss')-a.create_time)*24*3600 >= 0
and (to_date(a.QUOTE_TIME,'yyyy-mm-dd hh24:mi:ss')-a.create_time)*24*3600 <= 99999999
order by a.create_time desc
) v
where rownum <= 40)
where rnm >= 21
我的速度是13’现在还想更快点
第二,and (to_date(a.QUOTE_TIME, "yyyy-mm-dd hh24:mi:ss ")-a.create_time)*24*3600 >= 0
and (to_date(a.QUOTE_TIME, "yyyy-mm-dd hh24:mi:ss ")-a.create_time)*24*3600 <= 99999999
可做如下修改:将to_date(a.QUOTE_TIME, "yyyy-mm-dd hh24:mi:ss ") 赋给一个变量v_QUOTE_TIME ,然后变为:and v_QUOTE_TIME >=a.create_time
and v_QUOTE_TIME- 99999999/(24*3600) <=a.create_time 如果create_time有index,将会有很大提高
第三:oracle新语法:
select * from(
select * row_number() OVER(order by cof.createdatetime) as rank from co_cof cof
) a
where rank between 2 and 5 这是按照createdatetime 升序排列然后取第二到第五条记录,你可以自己仿照着改改看
and a.create_time <= to_date( "2007-09-26 23:59:59 ", "yyyy-mm-dd hh24:mi:ss ")
替换为
and a.create_time (between to_date( "2007-08-01 00:00:00 ", "yyyy-mm-dd hh24:mi:ss ") and a.create_time <= to_date( "2007-09-26 23:59:59 ", "yyyy-mm-dd hh24:mi:ss "))
试试