mysql - 无法在mysql查询中获得左外部联接结果

  SELECT
        BB.NAME BranchName,
        VI.NAME Village,
        COUNT(BAC.CBSACCOUNTNUMBER) 'No.Of Accounts',
        SUM(BAC.CURRENTBALANCE) SumOfAmount,
        SUM(CASE
            WHEN transactiontype = 'C' THEN amount
            ELSE 0
        END) AS CreditTotal,
        SUM(CASE
            WHEN transactiontype = 'D' THEN amount
            ELSE 0
        END) AS DebitTotal,
        SUM(CASE
            WHEN transactiontype = 'C' THEN amount
            WHEN transactiontype = 'D' THEN - 1 * amount
            ELSE 0
        END) AS CurrentBalance
    FROM
        CUSTOMER CU,
        APPLICANT AP,
        ADDRESS AD,
        VILLAGE VI,
        BANKBRANCH BB,
        BANKACCOUNT BAC
            LEFT OUTER JOIN
        accounttransaction ACT ON BAC.CBSACCOUNTNUMBER = ACT.BANKACCOUNT_CBSACCOUNTNUMBER
            AND ACT.TRANDATE <= '2013-03-21'
            AND BAC.ACCOUNTOPENINGDATE < '2013-03-21'
            AND ACT.BANKACCOUNT_CBSACCOUNTNUMBER IS NOT NULL
    WHERE
        CU.CODE = AP.CUSTOMER_CODE
            AND BAC.ENTITY = 'CUSTOMER'
            AND BAC.ENTITYCODE = CU.CODE
            AND AD.ENTITY = 'APPLICANT'
            AND AD.ENTITYCODE = AP.CODE
            AND AD.VILLAGE_CODE = VI.CODE
            AND AD.STATE_CODE = VI.STATE_CODE
            AND AD.DISTRICT_CODE = VI.DISTRICT_CODE
            AND AD.BLOCK_CODE = VI.BLOCK_CODE
            AND AD.PANCHAYAT_CODE = VI.PANCHAYAT_CODE
            AND CU.BANKBRANCH_CODE = BB.CODE
            AND BAC.CBSACCOUNTNUMBER IS NOT NULL
            AND ACT.TRANSACTIONTYPE IS NOT NULL
    GROUP BY BB.NAME , VI.NAME;

这是我的资料
    我有两个表bankaccount和accountransactions表
    如果创建了帐户，它将转到bankaccount表，并且如果进行了任何交易，则在accounttrasactiosns表中记录各自的帐号，但是我想显示与该帐户存在于bankaccount中的分支对应的总帐号计数，可能是或在accounttransactions表中可能不可用。

最佳答案

我猜测您遇到的问题是，即使您使用的是accounttransaction，也无法获得LEFT JOIN表中没有数据的帐户的结果。如果是这样，则原因是因为您的加入条件包括AND ACT.BANKACCOUNT_CBSACCOUNTNUMBER IS NOT NULL，这使LEFT JOIN无效。您在WHERE子句中还有两个条件，我打赌不应该在那里。

您应该学习在编码中使用显式联接语法。如果这样做的话，您的代码将更加清晰。它将连接条件与WHERE子句分开，这做了非常不同的事情。我试图重写您的查询作为例证：

SELECT
    BB.NAME BranchName,
    VI.NAME Village,
    COUNT(BAC.CBSACCOUNTNUMBER) 'No.Of Accounts',
    SUM(BAC.CURRENTBALANCE) SumOfAmount,
    SUM(ACT.CurrentBalance) CurrentBalance,
    SUM(ACT.DebitTotal) DebitTotal,
    SUM(ACT.CreditTotal) CreditTotal

FROM   CUSTOMER CU

JOIN   APPLICANT AP
ON     AP.CUSTOMER_CODE = CU.CODE

JOIN   ADDRESS AD
ON     AD.ENTITYCODE = AP.CODE

JOIN   VILLAGE VI
ON     VI.CODE           = AD.VILLAGE_CODE
   AND VI.STATE_CODE     = AD.STATE_CODE
   AND VI.DISTRICT_CODE  = AD.DISTRICT_CODE
   AND VI.BLOCK_CODE     = AD.BLOCK_CODE
   AND VI.PANCHAYAT_CODE = AD.PANCHAYAT_CODE

JOIN   BANKBRANCH BB
ON     BB.CODE = CU.BANKBRANCH_CODE

JOIN   BANKACCOUNT BAC
ON     BAC.ENTITYCODE = CU.CODE

LEFT OUTER JOIN (
   SELECT BANKACCOUNT_CBSACCOUNTNUMBER,
          SUM(CASE
             WHEN transactiontype = 'C' THEN amount
             ELSE 0
             END) AS CreditTotal,
          SUM(CASE
             WHEN transactiontype = 'D' THEN amount
             ELSE 0
             END) AS DebitTotal,
          SUM(CASE
             WHEN transactiontype = 'C' THEN amount
             WHEN transactiontype = 'D' THEN - 1 * amount
             ELSE 0
             END) AS CurrentBalance
   FROM    accounttransaction
   WHERE   TRANDATE <= '2013-03-21'
   GROUP BY BANKACCOUNT_CBSACCOUNTNUMBER
   ) ACT

ON     ACT.BANKACCOUNT_CBSACCOUNTNUMBER = BAC.CBSACCOUNTNUMBER
   AND BAC.ACCOUNTOPENINGDATE < '2013-03-21'

WHERE  BAC.ENTITY = 'CUSTOMER'
   AND AD.ENTITY = 'APPLICANT'

GROUP BY BB.NAME , VI.NAME;

我从LEFT JOIN条件中删除了这一行

AND ACT.BANKACCOUNT_CBSACCOUNTNUMBER IS NOT NULL

我从WHERE子句中删除了这两行

AND BAC.CBSACCOUNTNUMBER IS NOT NULL
AND ACT.TRANSACTIONTYPE IS NOT NULL

如果那不能解决您的问题，请修改您的问题以进一步说明。

更新：基于注释，查询被修订以使用派生表按帐户计算借方，贷方和当前余额。

还请注意BAC.ACCOUNTOPENINGDATE < '2013-03-21'条件在左联接上的位置。按照书面规定，无论开立日期如何，这将返回所有帐户。如果只想显示在该日期之前开立的帐户，则此条件应移至WHERE子句。

关于mysql - 无法在mysql查询中获得左外部联接结果，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/17277899/