mysql+求和去重复项,MySQL：如何做多个联接时求和非重复值

I try to SUM values from columns, from a query which contains some JOINS.

Example:

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;

I get this result :

+------------+-------------------------+-------------------------+

| 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 |

+------------+-------------------------+-------------------------+

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;

Result :

+------------+-------------------------+-------------------------+

| product_id | stock_bought_last_month | stock_already_commanded |

+------------+-------------------------+-------------------------+

| 9507 | 74 | 75 |

+------------+-------------------------+-------------------------+

The result wanted is :

+------------+-------------------------+-------------------------+

| 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