来自两个mysql表列的总和 [英] sum from both mysql table columns
本文介绍了来自两个mysql表列的总和的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有两个mysql表:
I have two mysql tables:
table: loan
--------------------------------------------------------
id | advance_id | loan_amount | deposit_amount | date
--------------------------------------------------------
1 | 22556678 | 5000 | 0 | 2015-02-06
2 | 22556678 | 5000 | 0 | 2015-02-07
--------------------------------------------------------
table: advance
--------------------------------------------------------
id | advance_id | advance_amount | purpose | date
--------------------------------------------------------
1 | 22556678 | 20000 | purchase | 2015-01-30
2 | 22556678 | 10000 | purchase | 2015-01-31
我想从表中获取'loan_amount'的总和:借出和从表中获取'advance_amount'的总和:在一个由INNER JOIN加入的mysql查询中前进.
I want to get sum of 'loan_amount' from table: loan and sum of 'advance_amount' from table: advance in one mysql query joined by INNER JOIN.
如何从两个表的列中求和?
How to get the sum from columns of two tables?
SELECT
a.advance_id,
SUM(a.advance_amount) AS adv_amount,
a.purpose,
a.date,
SUM(l.loan_amount) AS loan_amount
FROM
advance AS a
INNER JOIN loan AS l
ON a.advance_id = l.advance_id
GROUP BY a.advance_id
HAVING SUM(l.loan_amount) - SUM(l.deposit_amount) > 0
推荐答案
您可以将查询重写为
SELECT
a.advance_id,
SUM(a.advance_amount) AS adv_amount,
a.purpose,
a.date,
l.loan_amount
FROM
advance AS a
INNER JOIN
(
SELECT advance_id,SUM(deposit_amount) AS loan_amount
FROM loan
GROUP BY advance_id
HAVING SUM(loan_amount) - SUM(deposit_amount) > 0
) AS l USING(advance_id)
GROUP BY a.advance_id
这篇关于来自两个mysql表列的总和的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文