select count(distinct(f_service_id)) from wb_cust_contact_t
where f_contact_result = 1
and f_stat_time>=to_date('2011-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and f_stat_time<=sysdate
and f_event = '14'
and f_service_kind not in('11','8','10')怎么用not exists 代替not in
where f_contact_result = 1
and f_stat_time>=to_date('2011-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and f_stat_time<=sysdate
and f_event = '14'
and f_service_kind not in('11','8','10')怎么用not exists 代替not in
-->
and f_service_kind <>'11' and f_service_kind <> '8' and f_service_kind <> '10'
-- 你实在喜欢倒腾的话,就这样写啦:
with a as (select '11' as f_service_kind from dual
union all select '8' as f_service_kind from dual
union all select '10' as f_service_kind from dual )
select count(distinct(f_service_id)) from wb_cust_contact_t
where f_contact_result = 1
and f_stat_time>=to_date('2011-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and f_stat_time<=sysdate
and f_event = '14'
and not exists (select 1 from a where a.f_service_kind=wb_cust_contact_t.f_service_kind);
感觉没有太大需要改动的,楼主可以把where条件的顺序调下,
固定值和能去除大量数据的条件放到最后面,把需要作运算的条件往前放!
select count(distinct(f_service_id)) from wb_cust_contact_t
where f_contact_result = 1
and f_stat_time>=to_date('2011-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and f_stat_time<=sysdate
and f_event = '14'
and f_service_kind <>'11' and f_service_kind <> '8' and f_service_kind <> '10'
select count(distinct(f_service_id)) from wb_cust_contact_t
where f_contact_result = 1
and f_event = '14'
and f_stat_time>=to_date('2011-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and f_stat_time<=sysdate
and f_service_kind not in('11','8','10');
另,where后的第一个字段一定要是有索引的;in和exists在这里没法替换,而且就算能替换,也不一定能替换效率,in和EXISTS的效率要看实际的数据量,是相对的,不是绝对的。希望对你有帮助……