从表1中选择表2.列和表3.基于表accountNumbers的

本文关键字：于表 accountNumbers 选择 从表 | 更新日期: 2023-09-27 18:15:40

我在过去的两天里有这个查询的挑战。我到处找，就是找不到。我有3张桌子;customers, transactions和withdrawals。客户表有AccountNumber、Names等，交易有FK AccountNumber和Amount，取款有FK AccountNumber和Amount。

我只想选择AccountNumber，客户名称和SUM对应的交易金额和按AccountNumber SUM提款金额AccountNumber

当前我被迫运行

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

将数据加载到datatable中并循环到select:

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

: 

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

请建议一个单一的查询，以帮助做到这一点。

从表1中选择表2.列和表3.基于表accountNumbers的

试试这个查询:

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
相关文章：