DB2 SQL 求大大帮忙解决 在线等 谢谢

[arsenal1999]

直接上SQL

SELECT * FROM Business_duebill BD where not exists (select 1 from Classify_Record CR where CR.ObjectNo = DB.SerialNO)



其中 DB.SerialNO ， CR.ObjectNo 均为索引

[arsenal1999]

来人啊 谢谢

[zhaojianmi1]

你的问题是什么

[zhaojianmi1]

这个语句没问题啊

[arsenal1999]

left join 结果没有数据了

[arsenal1999]

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 OperateOrgName
from 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.SerialNO
where (BD.BusinessType like '1%' and BD.BusinessType not like '1020%')
and BD.PutOutDate < '2011/03/32' and BD.balance >0
and BD.SerialNo  is null
and BD.OPERATEORGID in (select BelongOrgID from ORG_BELONG where OrgID = '1000')

[arsenal1999]

能看看出什么问题了么

[zhaojianmi1]

原sql：

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 OperateOrgName
from 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.SerialNO
where BD.BusinessType like '1%'
  and BD.BusinessType not like '1020%'
  and BD.PutOutDate < '2011/03/32'
  and BD.balance >0
  and BD.SerialNo is null
  and BD.OPERATEORGID in (
        select BelongOrgID from ORG_BELONG where OrgID = '1000'
      )

试试下面的sql，有结果吗

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 OperateOrgName
from BUSINESS_DUEBILL BD
where BD.BusinessType like '1%'
  and BD.BusinessType not like '1020%'
  and BD.PutOutDate < '2011/03/32'
  and BD.balance >0
  and BD.OPERATEORGID in (
        select BelongOrgID from ORG_BELONG where OrgID = '1000'
      )

[zhaojianmi1]

应该这么写吧

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 OperateOrgName
from 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.SerialNO
where 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'
      )

[arsenal1999]

恩 我也试出来了 谢谢各位的关注！结贴了 万分感谢

[arsenal1999]

执行起来特别慢 有没有其他方法代替啊

[arsenal1999]

要上班了--！中午再来看看

[yangxiao_jiang]

改成left join，你的那个表记录多？

[arsenal1999]

我想查在Business_duebill中 不在表Classify_Record中的信息，Business_duebill中有10W Classify_Record应该有200W左右吧

[arsenal1999]

因为每3个月对Business_duebill做一次数据分析，分析的结果存储在Classify_Record中

[WWWWA]

SELECT bd.* FROM Business_duebill BD
left join Classify_Record CR on CR.ObjectNo = DB.SerialNO
where DB.SerialNO is null

检查一下执行 计划

[wwwwb]

检查一下索引情况

[llywuchen]

路过。。。。。。。。。。

[zhaojianmi1]

SELECT BD.*
FROM Business_duebill BD  
     left join Classify_Record CR on CR.ObjectNo = DB.SerialNO
where DB.SerialNO is null

这样快不快

[WWWWA]

检查一下执行 计划，看看索引是否用到

or

SELECT * FROM Business_duebill BD where SerialNO not in (select ObjectNo from Classify_Record )