请帮助优化以下查询:10万条以上的记录select count(distinct id) from BOOK_ORDER a , (select BOOK_no no,max(create_time) time from BOOK_ORDER group by (BOOK_no, BOOK_CJ)) b
where a.BOOK_no = b.no and a.create_time = b.time
and STATUS = 'CH'
where a.BOOK_no = b.no and a.create_time = b.time
and STATUS = 'CH'
from (select BOOK_no no, BOOK_CJ,max(create_time) time
from BOOK_ORDER
where STATUS = 'CH'
group by BOOK_no, BOOK_CJ
)tb
BOOK_no ,BOOK_CJ ,create_time ,STATUS 1 1 2009-01-01 CH
1 1 2009-01-02 DH 2 2 2009-01-01 DH
2 2 2009-01-02 CH这个表查询结果应该只有1条
以BOOK_no ,BOOK_CJ 分组,最大的create_time 时间那条记录的STATUS必须是CH,才为所选的
where a.BOOK_no = b.no and a.create_time = b.time and STATUS = 'CH'
( select id row_number(partintion by id order by id)rn
from
BOOK_ORDER a ,
)
where rn=1 --试试会不会快些
from (select BOOK_no,
BOOK_CJ,
create_time,
STATUS,
row_number() over(partition by book_no, book_cj order by create_time desc) rn
from BOOK_ORDER)
where rn = 1
and STATUS = 'CH';
where a.BOOK_no = b.no and a.create_time = b.time
一般人不会帮我优化的,因为要获取你的实际信息还要跟你交流.
能力强的人一般时间少,呵呵