mysql - SQL语句结果的前三行有一个乘数

我在MySQL问题上有一条SQL语句，如下所示：

Select Products.ProductID, Products.ModelNumber, Products.SerialNumber, Products.Description, Products.ListPrice,
        Sum(OrderDetails.Quantity) AS QuantityOrdered, Sum(Inventory.Quantity) AS QuantityOnHand

From Products
LEFT JOIN OrderDetails USING(ProductID)
LEFT JOIN Inventory USING(ProductID)

group by Products.ProductID, Products.ModelNumber, Products.SerialNumber, Products.Description, Products.ListPrice;

SQL语句工作正常，除了返回的前三行在Sum(OrderDetails.Quantity) AS QuantityOrdered列上有一个乘数。第一行的乘数为4，第二行的乘数为3，第三行的乘数为2。其他每一行都很好，并且Sum(Inventory.Quantity) AS QuantityOnHand列返回正确的数字，没有乘数。

我已经编辑了OrderDetails表中与ProdcutID有关的值，这些值与前三行匹配。我将这些数量分别更改为1，分别得到4、3和2。

最佳答案

您需要单独的count（），请尝试如下子查询：

Select Products.ProductID, Products.ModelNumber, Products.SerialNumber, Products.Description, Products.ListPrice
, (select Sum(Quantity) from OrderDetails where ProductID = Products.ProductID) AS QuantityOrdered
, (select Sum(Quantity) from Inventory where ProductID = Products.ProductID) AS QuantityOnHand
From Products

或者如果您只需要订购产品：

Select Products.ProductID, Products.ModelNumber, Products.SerialNumber, Products.Description, Products.ListPrice
, Sum(Quantity) AS QuantityOrdered
, (select Sum(Quantity) from Inventory where ProductID = Products.ProductID) AS QuantityOnHand
From Products JOIN OrderDetails USING(ProductID)
group by Products.ProductID, Products.ModelNumber, Products.SerialNumber, Products.Description, Products.ListPrice

关于mysql - SQL语句结果的前三行有一个乘数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/36121128/