我尝试从包含一些JOINS的查询中的列中SUM值.
例:
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进行分组:
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 |
+------------+-------------------------+-------------------------+
我如何忽略重复?当然,行数可以改变!