这是我的问题
SELECT
`product_id`,
`product_quantity`,
`product_price`,
`product_price` * `product_quantity` AS `product_total`,
`purch_price`,
`purch_price` * `product_quantity` AS `purch_total`
FROM
`master_stock`
WHERE `product_id`='SSK-SQ2001-20W-6500K'
GROUP BY
`id`
ORDER BY
`id`
这是桌子
产品_ID产品_数量产品_价格产品_总采购_价格采购_总采购
SQ 2001 100 167 1670 134 13400
SQ 2001 100 167 1670 135 13500
现在我想得到这样的结果（因为它有相同的id但是购买价格不同）
product_id产品_数量产品_总采购_总采购
SQ 2001 200 334000 26900
我尝试了却得不到结果的事
SELECT
ms.id,
ms.product_id,
ms.product_name,
sum(ms.product_quantity) AS total_quantity,
ms.product_price*ms.product_quantity AS product_total,
ms.purch_price*ms.product_quantity AS purch_total
FROM
master_stock ms
GROUP BY
ms.product_id
ORDER BY
ms.product_id;
这是我的结果
请帮忙，提前谢谢
如果我正确理解你的问题，你需要先把数量求和，然后再把它与价格相乘。 否则，你只需将它与其中一个量相乘。 这个问题的解决办法如下:
SELECT
ms.id,
ms.product_id,
ms.product_name,
sum(ms.product_quantity) AS total_quantity,
ms.product_price*sum(ms.product_quantity) AS product_total,
ms.purch_price*sum(ms.product_quantity) AS purch_total
FROM
master_stock ms
GROUP BY
ms.product_id
ORDER BY
ms.product_id;
fiddle:http://sqlfiddle.com/#！9/6f16ce/49/0
select distinct (ms.product_id) ,
ms.product_name,
(select sum(product_quantity)
from master_stock
where master_stock.product_id = ms.product_id ) as total_quantity,
ms.product_price*ms.product_quantity AS product_total,
ms.purch_price*ms.product_quantity AS purch_total
FROM
master_stock ms
GROUP BY
ms.product_id
ORDER BY
ms.product_id;
