MySQL:做多个联接时,我如何求和不重复值 [英] MySQL: How do I SUM non duplicates values when doing multiples JOINS
本文介绍了MySQL:做多个联接时,我如何求和不重复值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我尝试从包含一些JOINS的查询的列中求和.
I try to SUM values from columns, from a query which contains some JOINS.
示例:
SELECT
p.id AS product_id,
SUM(out_details.out_details_quantity) AS stock_bought_last_month,
SUM(order_details.order_quantity) AS stock_already_commanded
FROM product AS p
INNER JOIN out_details ON out_details.product_id=p.id
INNER JOIN order_details ON order_details.product_id=p.id
WHERE p.id=9507
GROUP BY out_details.out_details_pk, order_details.id;
我得到这个结果:
+------------+-------------------------+-------------------------+
| product_id | stock_bought_last_month | stock_already_commanded |
+------------+-------------------------+-------------------------+
| 9507 | 22 | 15 |
| 9507 | 22 | 10 |
| 9507 | 10 | 15 |
| 9507 | 10 | 10 |
| 9507 | 5 | 15 |
| 9507 | 5 | 10 |
+------------+-------------------------+-------------------------+
现在,我想对这些值求和,但是当然有重复项.我还必须按product_id分组:
Now, I want to SUM the values, but of course there are duplicates. I also have to group by product_id :
SELECT
p.id AS product_id,
SUM(out_details.out_details_quantity) AS stock_bought_last_month,
SUM(order_details.order_quantity) AS stock_already_commanded
FROM product AS p
INNER JOIN out_details ON out_details.product_id=p.id
INNER JOIN order_details ON order_details.product_id=p.id
WHERE p.id=9507
GROUP BY p.id;
结果:
+------------+-------------------------+-------------------------+
| product_id | stock_bought_last_month | stock_already_commanded |
+------------+-------------------------+-------------------------+
| 9507 | 74 | 75 |
+------------+-------------------------+-------------------------+
想要的结果是:
+------------+-------------------------+-------------------------+
| product_id | stock_bought_last_month | stock_already_commanded |
+------------+-------------------------+-------------------------+
| 9507 | 37 | 25 |
+------------+-------------------------+-------------------------+
如何忽略重复项?当然,行数可以更改!
How do I ignores duplicates? Of course, the count of lines can change!
推荐答案
Select P.Id
, Coalesce(DetailTotals.Total,0) As stock_bought_last_month
, Coalesce(OrderTotals.Total,0) As stock_already_commanded
From product As P
Left Join (
Select O1.product_id, Sum(O1.out_details_quantity) As Total
From out_details As O1
Group By O1.product_id
) As DetailTotals
On DetailTotals.product_id = P.id
Left Join (
Select O2.product_id, Sum(O2.order_quantity) As Total
From order_details As O2
Group By O2.product_id
) As OrderTotals
On OrderTotals.product_id = P.id
Where P.Id = 9507
这篇关于MySQL:做多个联接时,我如何求和不重复值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文