直接上SQL
SELECT * FROM Business_duebill BD where not exists (select 1 from Classify_Record CR where CR.ObjectNo = DB.SerialNO)
其中 DB.SerialNO ， CR.ObjectNo 均为索引
------解决方案--------------------------------------------------------
SELECT bd.* FROM Business_duebill BD
left join Classify_Record CR on CR.ObjectNo = DB.SerialNO
where DB.SerialNO is null
检查一下执行 计划
------解决方案--------------------------------------------------------
- SQL code
SELECT BD.*FROM Business_duebill BD left join Classify_Record CR on CR.ObjectNo = DB.SerialNOwhere DB.SerialNO is null
------解决方案--------------------------------------------------------
检查一下执行 计划，看看索引是否用到
or
SELECT * FROM Business_duebill BD where SerialNO not in (select ObjectNo from Classify_Record )
------解决方案--------------------------------------------------------
应该这么写吧
- SQL code
select BD.SerialNo ,BD.CustomerID ,BD.CustomerName ,getBusinessName(BD.BusinessType) as BusinessTypeName ,BD.BusinessSum ,BD.Balance,BD.OperateUserID ,BD.OperateOrgID ,getUserName(BD.OperateUserID) as OperateUserName ,getOrgName(BD.OperateOrgID) as OperateOrgNamefrom BUSINESS_DUEBILL BD left join ( select ObjectNo from CLASSIFY_RECORD CR where finishdate is not null and CR.ObjectType='BusinessDueBill' and AccountMonth='2011/03' ) CR on CR.ObjectNo = BD.SerialNOwhere BD.BusinessType like '1%' and BD.BusinessType not like '1020%' and BD.PutOutDate < '2011/03/32' and BD.balance >0 and CR.ObjectNo is null and BD.OPERATEORGID in ( select BelongOrgID from ORG_BELONG where OrgID = '1000' )