mysql - MySQL的。从SUM(c.Amount)中扣除SUM(d.Amount)，并根据(取决于)另一列中的值进行分组

这是MySQL表

Amount | DebitAccount | CreditAccount | TransactionPartnerName
--------------------------------------------------------------
 1     |     5610     |     5720      |    name one
 150   |     7210     |     5610      |    name one

需要得到这样的东西

name one result for account 5610 = 149.
((`CreditAccount` for `5610`) - (`DebitAccount` for `5610`)) = 149

换一种说法：

1）对所有Amounts求和，其中DebitAccount是5610并且TransactionPartnerName是name one

2）对所有Amounts求和，其中CreditAccount是5610并且TransactionPartnerName是name one

3）从CreditAccount总和中减去DebitAccount总和

尝试过此代码

SELECT TransactionPartnerName, SUM(Amount)
FROM ( SELECT * FROM 2_1_journal) DATA
WHERE (CAST(DebitAccount AS UNSIGNED) IN (?) OR CAST(CreditAccount AS UNSIGNED) IN (?))
GROUP BY TransactionPartnerName

但是结果是帐户5610获得值151。这表示SUM(Amount)和DebitAccount中的CreditAccount。

试图思考如何修改。

尝试过这样的事情

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

FROM

(
SELECT DebitAccount,

SUM( Amount ) AS Amount FROM
( SELECT * FROM 2_1_journal) DATA

GROUP BY DebitAccount
) d ON (AccountNumber = d.DebitAccount)

(
SELECT CreditAccount,
SUM( Amount ) AS Amount FROM
( SELECT * FROM 2_1_journal) DATA
GROUP BY CreditAccount
) c ON (AccountNumber = c.CreditAccount)

GROUP BY TransactionPartnerName

但是得到空白页。

请提出需要纠正的建议

最佳答案

SELECT TransactionPartnerName, SUM(if(CAST(DebitAccount AS UNSIGNED) IN (?), -Amount, Amount))
FROM 2_1_journal DATA
WHERE (CAST(DebitAccount AS UNSIGNED) IN (?) OR CAST(CreditAccount AS UNSIGNED) IN (?))
GROUP BY TransactionPartnerName

尝试这个


编辑：删除内部查询