多个mysql表列的求和、减法和连接 [英] Sum, Subtract and Join of multiple mysql table columns

问题描述

我有四个 mysql 表 client、transaction、other_loan 和 payment.我想从表 transaction + sum 中获取 load_amount 和 additional 的 sumamount 从 other_loan 和 subtract 到 payment_amount 的 sum 在表 <代码>付款.我怎样才能实现它?

<块引用>

我想要的结果:

<代码>>身份证 |姓名 |数量>1 |罗宾 |8718>2 |雷纳尔多 |21>3 |利奥马尔 |0

<块引用>

我的桌子:交易

<代码>>时间 |编号|日期 |负载量 |额外的>1 |1 |2018-12-01 |90 |0>2 |1 |2018-12-07 |90 |0>3 |2 |2018-12-08 |49 |2

<块引用>

表:other_loan

<代码>>类 |编号|金额 |日期>1 |1 |7928 |2018-12-10>2 |1 |750 |2018-12-10

<块引用>

表格:付款

<代码>>pid |id |付款金额 |日期>1 |1 |50 |2015-12-10>2 |1 |90 |2015-12-10>3 |2 |30 |2015-12-10

<块引用>

表:客户端

<代码>>编号 |姓名 |>1 |罗宾 |>2 |灰姑娘 |>3 |利奥马尔 |

解决方案

因为您有多个交易，每个客户的其他贷款金额和付款，您不能直接 JOIN 表因为这会导致行的复制，从而导致不正确的值.相反，我们在执行 JOIN 之前 以客户端为基础SUM 每个表中的所有值.此外，由于某些客户端在每个表中都没有条目，因此您必须在结果上使用 LEFT JOINs 和 COALESCE 以便空行不会导致 SUM 变为NULL.此查询应为您提供所需的结果:

选择 c.id, c.name,COALESCE(t.transactions, 0) + COALESCE(o.amounts, 0) - COALESCE(p.payments, 0) AS 金额来自客户 cLEFT JOIN (SELECT id, SUM(load_amount) + SUM(additional) AS 交易从交易GROUP BY id) t on t.id = c.idLEFT JOIN (SELECT id, SUM(amount) AS amountFROM other_loanGROUP BY id) o ON o.id = c.idLEFT JOIN (SELECT id, SUM(payment_amount) AS 付款从付款GROUP BY id) p ON p.id = c.id按 c.id 分组

输出(用于您的样本数据):

id 名称 数量1 罗宾 87182灰姑娘213 利奥马尔 0

SQLFiddle 演示

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     | 

解决方案

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

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