两张表之间的Mysql操作，并希望在一张表中输出

Question

我在 amount_to_pay 表数据中有两个表 amount_to_pay 和 paid_amount 根据 center_id 和 插入university_id 他为哪个大学的哪个中心支付了多少费用，并在 paid_amount 数据中插入了他为哪个大学的哪个中心支付了多少费用，我希望中心的 Netty 待定具体大学如下面第三张表所示。

这是第一个表格amount_to_pay

+---------+--------------+-------------+
|center_id|university_id |amount_topay |
+---------+--------------+-------------+
| 1       | 6            |29000        |
+---------+--------------+-------------+
| 1       | 7            |19700        |
+---------+--------------+-------------+
| 2       | 6            |9000         |
+---------+--------------+-------------+

这是我的第二张表 paid_amount

+---------+--------------+------------+
|center_id|university_id |amount_paid |
+---------+--------------+------------+
| 1       | 6            |9000        |
+---------+--------------+------------+
| 2       | 6            |5000        |
+---------+--------------+------------+

并希望输出如下表

+---------+--------------+-------+
|center_id|university_id |amount |
+---------+--------------+-------+
| 1       | 6            |20000  |
+---------+--------------+-------+
| 1       | 7            |19700  |
+---------+--------------+-------+
| 2       | 6            |4000   |
+---------+--------------+-------+

上面的 amount_topay 列是 amount_topay 和按 center_id 和 university_id 分组的总和在第二个表中，amount_paid 也是 amount_paid 列和按 center_id 和 university_id 分组的总和。

Answer 1

您可以连接表格并减去金额。请注意，您应该使用 left join，这样您就不会错过尚未付款的条目:

SELECT    a.center_id,
          a.university_id,
          a.amount_to_pay - COALESCE(p.amount_paid, 0) AS amount
FROM      amount_to_pay a
LEFT JOIN paid_amount p ON a.center_id = p.center_id AND 
                           a.university_id = p.university_id