MySQL数据库所需金额

对你需要的东西有一点干净的方法

SELECT z.*,
(z.totalA - z.in) AS difference
FROM (
    SELECT a.idA, a.currencyA, a.totalA,
    IF(b.idB IS NOT NULL, SUM(b.amountA), 0) AS `in`
    FROM tableA a
    LEFT JOIN tableB b ON a.idA = b.idA
    GROUP BY a.idA
) z;

输出

idA currencyA   totalA  in  difference
1      usd       1000   550   450
2      usd       2000   700   1300
3      eur       3000   600   2400
4      usd       4000    0    4000

Working Demo

我相信这样的方法可以奏效:

select 
    tableA.idA, 
    tableA.currencyA as currency, 
    tableA.totalA as total, 
    case when (sum(tableB.amountB) is null) then 0 else sum(tableB.amountB) end as 'in',
    case when (sum(tableB.amountB) is null) then tableA.totalA else 
    tableA.totalA-sum(tableB.amountB) end as 'difference'
from 
    tableA left outer join tableB on tableA.idA=tableB.idA
group by tableA.idA;