select distinct jgbm,cfh from sjpt_cfls  
where HANDLETIME a between to_date('2012-04-01','yyyy-mm-dd') and to_date('2012-04-12','yyyy-mm-dd') and kss=2 and EXISTS ( SELECT 1 
FROM  HANDLETIME b  WHERE a.jgbm = b.jgbm)

------解决方案--------------------
这样呢

SQL code

select jgbm,cfh,count(*) 
from sjpt_cfls  
where HANDLETIME between date'2012-04-01' and date'2012-04-12' and kss=2 
group by jgbm,cfh 
having count(*) = 1

------解决方案--------------------
SQL code
select jgbm,cfh from sjpt_cfls  
where HANDLETIME a between to_date('2012-04-01','yyyy-mm-dd') and to_date('2012-04-12','yyyy-mm-dd') and kss=2 and EXISTS ( SELECT 1 
FROM  HANDLETIME b  WHERE a.jgbm = b.jgbm)

------解决方案--------------------
SQL code

 select jgbm,cfh 
 FROM sjpt_cfls a
 where exists (select 1 from sjpt_cfls b where a.jgbm=b.jgbm) and 
        HANDLETIME between date'2012-04-01' and date'2012-04-12' and kss=2

------解决方案--------------------
ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.  

例如:  

　
SQL code
　
 --(低效) 
　　SELECT … FROM EMP E WHERE SAL > 50000 AND JOB = ‘MANAGER’ AND 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO); 

　--(高效) 
　　SELECT … FROM EMP E WHERE 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO) AND SAL > 50000 AND JOB = ‘MANAGER’

------解决方案--------------------
探讨

引用:
SQL code


select jgbm,cfh
FROM sjpt_cfls a
where exists (select 1 from sjpt_cfls b where a.jgbm=b.jgbm) and
HANDLETIME between date'2012-04-01' and date'2012-04-12' and kss=2


……


------解决方案--------------------
用in看效率如何

SQL code

--假设主键或者唯一字段 s_id
 select jgbm,cfh 
 FROM sjpt_cfls 
 where s_id in (select max(s_id) from sjpt_cfls group by jgbm,cfh) and 
        HANDLETIME between date'2012-04-01' and date'2012-04-12' and kss=2