SQL 查询返回双倍值

debugcn 发表于 Dev

用户2584076

以下 SQL 查询正在生成 double-up netincome：

lead.leadId,
    sum(ifnull(applicantEmployment.netIncome,0)),
   sum(ifnull(applicantIncome.amount,0))
FROM
  lead LEFT OUTER JOIN applicant ON (lead.leadId=applicant.leadId  AND  lead.deleted= 0  AND  applicant.deleted = 0)
   LEFT OUTER JOIN applicantEmployment ON (applicantEmployment.leadApplicantId=applicant.leadApplicantId  AND  applicant.deleted = 0  AND  applicantEmployment.deleted =0)
   LEFT OUTER JOIN applicantIncome ON (applicantIncome.leadApplicantId=applicant.leadApplicantId  AND  applicant.deleted = 0  AND  applicantIncome.deleted=0)
WHERE
  lead.leadId  =  800
GROUP BY
  lead.leadId, 
  applicant.leadApplicantId

上述查询结果为：

但是，如果您查看下表，sum(ifnull(applicantEmployment.netIncome,0))应该是：

= 10310.31+23380
= 33690.31

但它出来为44000.62这10310.31+10310.31+23380

表值：

因此，它使申请人 id 5339 的净收入翻了一番，申请人收入表中有 2 行。

它是任何类型的 SQL 陷阱吗？

我为 Bac 工作

是联接的正常行为。看看之前的表SUM和GROUP BY，它会变得更加清晰。您可以使用不同的方法来解决它。其中之一是在后面使用子查询SELECT

SELECT
   lead.leadId, 
   applicant.leadApplicantId,
   (
      SELECT sum(ifnull(applicantEmployment.netIncome,0))
      FROM applicantEmployment
      WHERE (applicantEmployment.leadApplicantId=applicant.leadApplicantId  AND  applicant.deleted = 0  AND  applicantEmployment.deleted =0)
   ) netincomeSum,
   (
      SELECT sum(ifnull(applicantIncome.amount,0))
      FROM applicantEmployment 
      LEFT OUTER JOIN applicantIncome ON (applicantIncome.leadApplicantId=applicant.leadApplicantId  AND  applicant.deleted = 0  AND  applicantIncome.deleted=0)
      WHERE lead.leadId=applicant.leadId  AND  applicant.deleted = 0  
   ) incomeSum
FROM lead 
LEFT OUTER JOIN applicant ON (lead.leadId=applicant.leadId  AND  lead.deleted= 0  AND  applicant.deleted = 0)
WHERE lead.leadId  =  800 and lead.deleted= 0

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。