如何使用MySQL在一次查询中使用2个表数据并在2个表数据的基础上创建一个新列

Question

我有一个表包含人的详细信息，一个是货币的详细信息，一个是人的债务，还有一个是已付的债务。
我想做一个查询来显示剩余债务，比如，如果某人借了100美元和100,000伊拉克第纳尔，然后又借了250美元，过了一段时间他还了150美元和40,000伊拉克第纳尔，我想查询显示某人在两行中剩余债务，一行是200美元，另一行是60,000伊拉克第纳尔，因此剩余债务金额为sum of debt_amount-sum of paid_amount一种货币和另一种货币。

我可以为每个人做两种货币的求和，所以它将两个债务（100美元和250美元）显示为一个350美元，但造成问题的是，我不知道如何用MySQL实现基于债务和已付债务的剩余金额的计算。

获取所有债务查询：

SELECT 
    people.id, people.name, SUM(debt_people.amount) AS debt, debt_people.cur_id AS currency_id
FROM
    debt_people 
        INNER JOIN
    people  ON people.id = debt_people.people_id
GROUP BY debt_people.people_id , debt_people.cur_id;

正在获取所有已付款部门查询：

SELECT 
    people.id,
    people.name,
    SUM(payment_people.amount) AS Payment,
    payment_people.cur_id AS currency_id
FROM
    payment_people
        INNER JOIN
    people  ON people.id = payment_people.people_id
GROUP BY payment_people.people_id , payment_people.cur_id;

Answer 1

我认为您希望在加入之前使用全部联合:

SELECT p.id, p.name, dp.cur_id,
       SUM(dp.debt) AS debt, SUM(dp.payment) as payment,
       SUM(dp.debt) - SUM(dp.payment) as remaining
FROM people p JOIN
     ((SELECT d.people_id, d.amount as debt, 0 as payment, d.cur_id
       FROM dept_people d
      ) UNION ALL
      (SELECT p.people_id, 0, p.amount as payment, p.cur_id
       FROM payment_people d
      )
     ) dp
     ON p.id = dp.people_id
GROUP BY p.id, p.name, dp.cur_id;