日期:2014-05-16 浏览次数:20827 次
select companyid bmdm1,count(distinct claimno) pretidiao from v_claimInfo2010 where isPreResearch='1' and (status='5') and (trunc(endorOverDate,'DD') between to_date('2010-01-01','YYYY-MM-DD') and to_date('2010-12-31','YYYY-MM-DD')) and not exists (select 1 from claimCoordinateRes where type='2' and claimCoordinateRes.claimno=v_claimInfo2010.claimno) group by companyid order by companyid;
------解决方案--------------------
--飞一般的感觉
SELECT a.Companyid Bmdm1, COUNT(DISTINCT a.Claimno) Pretidiao
FROM v_Claiminfo2010 a, Claimcoordinateres b
WHERE a.Claimno = b.Claimno
AND a.Endoroverdate >= DATE
'2010-01-01'
AND a.Endoroverdate <
To_Date('2010-12-31 00:00:01', 'yyyy-mm-dd hh:mi:ss')
AND a.Ispreresearch = '1'
AND b.TYPE = 2
AND a.Status = '5'
------解决方案--------------------
还有在你的claimCoordinateRes 表中的claimno列上建立一个索引