mysql。从 SUM(c.Amount) 中减去 SUM(d.Amount)，并按(取决于)其他列中的值进行分组

Question

这是 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获取值(value)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

但是得到空白页......

请指出需要纠正的地方

Answer 1

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

试试这个

编辑:删除内部查询