Sum, Subtract and Join of multiple mysql table col

问题:

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

Output (for your sample data):

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

Demo on SQLFiddle