mysql - SQL减去2条select语句的结果

Question

我有两个独立运行的查询:

SELECT patient.patient_id, sum(cost)
FROM patient, appointment 
WHERE patient.patient_id = appointment.patient_id 
GROUP BY patient.patient_id

---

SELECT sum(payment_amount)
FROM patient, payment
WHERE patient.patient_id = payment.patient_id
GROUP BY patient.patient_id

我想知道如何从第一个查询中减去第二个查询的结果以在我的 patient 表中创建一个 current_balance 字段？

Answer 1

将两个问题合二为一，然后减去 SUM。

SELECT pat.patient_id, SUM(cost) SUMCOST, SUM(payment_amount) SUMpayment_amount, SUM(cost) - SUM(payment_amount) current_balance 
FROM patient pat
INNER JOIN payment pay ON pat.patient_id = pay.patient_id 
INNER JOIN appointment app ON pat.patient_id = app.patient_id
GROUP BY pat.patient_id