c# - 根据表 accountNumbers 从 table1 sum table2.column sum table3.column 中选择

Question

过去 2 天我遇到了这个查询的挑战。我到处寻找，但一无所获。我有3张 table ； 客户、交易和提款。客户表有帐号、姓名等，交易有 FK 帐号和金额，提款有 FK 帐号和金额。

我只想选择 AccountNumber、客户姓名以及 SUM 相应的交易金额，并按 AccountNumber 对提款金额 AccountNumber 求和。

目前我被迫运行

SELECT AccountNumber,FirstName,LastName,Town,Contact
FROM customers
WHERE status = 1
ORDER BY AccountNumber ASC

将数据加载到数据表中并循环选择:

SELECT SUM(SusuAmount)
FROM transactions
WHERE AccountNumber = " + account + "
  AND Approval = 1`

还有这个:

SELECT SUM(ValueAmount)
FROM withdrawals
WHERE AccountNumber = " + account + "
  AND Approval = 1`

请建议一个查询来帮助完成一次此操作。

Answer 1

尝试这个查询:

SELECT cus.AccountNumber,cus.FirstName,cus.LastName,cus.Town,cus.Contract,Sum(trans.SusuAmount) AS TransactionAmount,SUM(widraw.ValueAmount) AS WithdrawalsAmount FROM customers AS cus 
LEFT JOIN transactions AS tran ON (trans.AccountNumber=cus.AccountNumber AND cus.Approval=1)
LEFT JOIN withdrawals AS widraw ON (widraw.AccountNumber=cus.AccountNumber AND widraw.Approval=1) 
WHERE cus.status=1 ORDER BY cus.AccountNumber ASC