如果DebitAccount == Credit Account，Mysql从SUM(Amount)Credit Account中减去SUM(Amount)Debit Account

Question

需要从贷方和借方帐号相同的借方总额中减去贷方总额。如果一个值只存在于借记账号，没有什么可以减去的，只需要得到这个值。

名为 18_7_ChartOfAccounts 的表如下所示:

ID | AccountNumber
-------------
1  | 2310
2  | 2380

另一个名为 2_1_journal 的表如下所示:

ID | Amount | DebitAccount | CreditAccount
-------------------------------------------
1  | 25.00  | 2310         | 2380
2  | 200.00 | 2310         | 2380
3  | 4.00   | 2380         | 2310
4  | 200.00 | 2380         | 2310
5  | 50.00  | 2380         | 2620

根据建议创建的查询对 Amount 列中的所有值求和，其中 DebitAccount 中的值相同。

查询执行以下操作:SUM(Amount) FROM table 2_1_journal WHERE DebitAccount in table 2_1_journal 是与表 18_7_ChartOfAccounts

中的 AccountNumber 相同

$query_debit_beginning = "SELECT SUM(j.Amount), j.DebitAccount FROM 18_7_ChartOfAccounts AS c LEFT JOIN 2_1_journal AS j ON (c.AccountNumber = j.DebitAccount) group by j.DebitAccount";

不明白如何从一个总和中减去另一个总和。

需要得到这样的输出

账号为2310，值为21.00 (25+200-4-200)

账号为2380，值为29.00(4+200+50-25-200)

帐号为 2620，值为 -50.00(0(DebitAccount 营业额)- 50(CreditAccount 营业额))

查询应如下所示:

(SUM(Amount) FROM table 2_1_journal WHERE DebitAccount in table 2_1_journal 与 AccountNumber 表 18_7_ChartOfAccounts)

然后

SUM(Amount) FROM table 2_1_journal WHERE CreditAccount in table 2_1_journal 与 相同AccountNumber 表 18_7_ChartOfAccounts

然后

如果 DebitAccount 等于 CreditAccount，从 SUM(Amount)CreditAccount 中减去 SUM(Amount)DebitAccount

不知道如何创建这样的查询。请指教。

根据建议尝试为选择添加条件。这是经过修改的。代码是否正确？尝试并显示在必要时有效

SELECT
a.AccountNumber, 
IFNULL( d.Amount, 0 ) - IFNULL( c.Amount, 0 ) AS Amount

FROM 18_7_ChartOfAccounts AS a

LEFT JOIN (
SELECT
    DebitAccount, RecordYear,
    SUM( Amount ) AS Amount
FROM 2_1_journal WHERE RecordYear = 2013
GROUP BY DebitAccount
) d ON (a.AccountNumber = d.DebitAccount)

LEFT JOIN (
SELECT
    CreditAccount, RecordYear,
    SUM( Amount ) AS Amount
FROM 2_1_journal WHERE RecordYear = 2013
GROUP BY CreditAccount
) c ON (a.AccountNumber = c.CreditAccount)

Answer 1

SELECT
    a.AccountNumber,
    IFNULL( d.Amount, 0 ) - IFNULL( c.Amount, 0 ) AS Amount

FROM 18_7_ChartOfAccounts AS a

LEFT JOIN (
    SELECT
        DebitAccount,
        SUM( Amount ) AS Amount
    FROM 2_1_journal
    GROUP BY DebitAccount
) d ON a.AccountNumber = d.DebitAccount

LEFT JOIN (
    SELECT
        CreditAccount,
        SUM( Amount ) AS Amount
    FROM 2_1_journal
    GROUP BY CreditAccount
) c ON a.AccountNumber = d.CreditAccount