多个mysql表列的总和，减和联接 [英] Sum, Subtract and Join of multiple mysql table columns

问题描述

我有四个mysql表client，transaction，other_loan和payment.我想从表transaction中获取load_amount和additional的sum +从other_loan和subtract中获取amount的sum到表payment_amount中的sum c3>.我该如何实现?

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

表:other_loan

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

表:客户

> id | Name       |  
> 1  | Robin      | 
> 2  | Cinderella |
> 3  | Leomar     | 

推荐答案

由于您有多个交易，每个客户的其他贷款额和还款额，因此您无法像往常一样直接对表进行JOIN导致复制行，从而导致错误的值.相反，我们在做JOIN之前，先在客户端基础上SUM在客户端中每个表中的所有值.另外，由于某些客户端在每个表中都没有条目，因此您必须在结果上使用LEFT JOIN和COALESCE，以便空行不会导致SUM变为NULL.此查询应为您提供所需的结果:

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
GROUP BY c.id

输出(用于示例数据):

Output (for your sample data):

id  name        amount
1   Robin       8718
2   Cinderella  21
3   Leomar      0

SQLFiddle上的演示

这篇关于多个mysql表列的总和，减和联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！