SQL查询以获取2个表中数据的总和 [英] Sql query to get data diffrence of total in 2 tables

问题描述

我有两个表:

1. booking-记录订单明细

id | booking_amount
-------------------
1  |            150
2  |            500
3  |            400

payment-记录订单付款

payment - records the payment for order

id | booking_id | amount
------------------------
1  |          1 |    100
2  |          1 |     50
2  |          2 |    100

我想查找所有付款未完成的预订.根据上述数据，我们希望答案为2,3，因为booking_id=1的付款总额与booking_table中的相应booking_amount相匹配.

I want to find all bookings where the payments are not complete. With the above data, we expect the answer to be 2,3, because the sum of payments for booking_id=1 matches the corresponding booking_amount in the booking_table.

推荐答案

要回答您的问题，您需要考虑两件事:

To answer your question, you have 2 things you need to think about :

1. 您想要每个预订行在表payment中的总金额

您想将booking_amount表与payment联接.

---

第1部分非常简单:

---

Part 1 is quite simple:

SELECT sum(amount) as TotalP, booking_id FROM payment GROUP BY booking_id

只是具有简单聚合函数的基本查询...

Just a basic query with a simple aggregate function...

对于第2部分，我们希望加入booking_amount和payment；基本的JOIN将是:

For part 2, we want to join booking_amount and payment; the basic JOIN would be:

SELECT * FROM booking b 
LEFT JOIN payment p ON b.id = p.booking_id

我们执行LEFT JOIN是因为我们可能有一些不在payment表中的预订.对于这些预订，您将获得NULL值.我们将使用COALESCE将NULL值替换为0.

We do a LEFT JOIN because we may have some booking who are not in the payment table. For those bookings, you will get NULL value. We will use a COALESCE to replace the NULL values by 0.

最终查询是这样:

SELECT b.id, COALESCE(TotalP, 0),  b.booking_amount
FROM
 booking b
LEFT JOIN
 (SELECT sum(amount) as TotalP, booking_id FROM payment GROUP BY booking_id) as T
ON  b.id = T.booking_id
WHERE COALESCE(TotalP, 0) < b.booking_amount

这篇关于SQL查询以获取2个表中数据的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！