无法在mysql查询中获得左外部联接结果 [英] Unable to get left outer join result in mysql query

问题描述

  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表中可能不可用.

Here is my information I have two tables bankaccount and accountransactions table If account is created it will go to bankaccount table and if any transaction is done so respective account number record in accounttrasactiosns table however I want to display the count of total account numbers respective to the branch which the account number existed in bankaccount and it is may or may not available in accounttransactions table.

推荐答案

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

I'm guessing that the problem you have is that you are not getting results for accounts that do not have data in your accounttransaction table, even though you are using a LEFT JOIN. If that is true, the reason is because your join condition includes AND ACT.BANKACCOUNT_CBSACCOUNTNUMBER IS NOT NULL, which defeats the LEFT JOIN. You also have two conditions in your WHERE clause that I bet should not be there.

您应该学习在编码中使用显式连接语法.如果这样做的话，您的代码将更加清晰.它将连接条件与WHERE子句分开，这做了非常不同的事情.我在重新编写您的查询时遇到了麻烦:

You should learn to use explicit join syntax in your coding. Your code will be much clearer if you do that; it separates the join conditions from the WHERE clause, which does a very different thing. I took a stab at re-writing your query as an illustration:

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条件中删除了这一行

I removed this line from the LEFT JOIN condition

AND ACT.BANKACCOUNT_CBSACCOUNTNUMBER IS NOT NULL

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

And I removed these two lines from the WHERE clause

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

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

If that does not solve your problem, please revise your question to explain further.

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

UPDATE: Based on comments, the query is revised to calculate the debit, credit, and current balance by account using a derived table.

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

Also note the placement of the BAC.ACCOUNTOPENINGDATE < '2013-03-21' condition on left join. As written, this will return all accounts regardless of the opening date. If you want to only show accounts that were opened before that date, this condition should be moved to the WHERE clause.

这篇关于无法在mysql查询中获得左外部联接结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！