mysql table multiple join_Sum, Subtract and Join of multiple mysql table columns

最新推荐文章于 2021-09-16 11:54:58 发布

村口王二狗

最新推荐文章于 2021-09-16 11:54:58 发布

阅读量72

点赞数

文章标签： mysql table multiple join

本文链接：https://blog.csdn.net/weixin_33841242/article/details/113721682

版权

在SQL中，当需要对多个表的数据进行加减运算时，不能直接JOIN，因为这会导致行重复，计算结果不准确。正确做法是先对每个表按客户ID分组求和，然后使用LEFT JOIN和COALESCE处理缺失值，避免结果为NULL。给出的SQL查询实现了这个功能，并给出了示例数据的输出结果。

摘要由CSDN通过智能技术生成

问题

I have four mysql tables client, transaction, other_loan and payment. I want to get the sum of load_amount and additional from table transaction + sum of amount from other_loan and subtract it to the sum of payment_amount in table payment. How can I achieve it?

Result I want:

> ID | Name | Amount

> 1 | Robin | 8718

> 2 | Reynaldo | 21

> 3 | Leomar | 0

My Tables:

transaction

> tid | id| date | load_amount | additional

> 1 | 1 | 2018-12-01 | 90 | 0

> 2 | 1 | 2018-12-07 | 90 | 0

> 3 | 2 | 2018-12-08 | 49 | 2

table: other_loan

> oid | id| amount | date

> 1 | 1 | 7928 | 2018-12-10

> 2 | 1 | 750 | 2018-12-10

table: payment

> pid |id | payment_amount | date

> 1 | 1 | 50 | 2015-12-10

> 2 | 1 | 90 | 2015-12-10

> 3 | 2 | 30 | 2015-12-10

table: client

> id | Name |

> 1 | Robin |

> 2 | Cinderella |

> 3 | Leomar |

回答1:

Because you have multiple transactions, other loan amounts and payments per customer, you can't do a straight JOIN of the tables to each other as it will cause replication of rows, resulting in incorrect values. Instead, we SUM all the values within each table on a client basis before doing the JOIN. Additionally, since some clients don't have entries in each table, you must use LEFT JOINs and COALESCE on the results so that empty rows don't cause SUMs to become NULL. This query should give you the results you want:

SELECT c.id, c.name,

COALESCE(t.transactions, 0) + COALESCE(o.amounts, 0) - COALESCE(p.payments, 0) AS amount

FROM client c

LEFT JOIN (SELECT id, SUM(load_amount) + SUM(additional) AS transactions

FROM transaction

GROUP BY id) t on t.id = c.id

LEFT JOIN (SELECT id, SUM(amount) AS amounts

FROM other_loan

GROUP BY id) o ON o.id = c.id

LEFT JOIN (SELECT id, SUM(payment_amount) AS payments

FROM payment

GROUP BY id) p ON p.id = c.id