SQL关系查询

Question

SELECT dbo.Customers.Name, dbo.Items.Name AS ItemName,SUM(dbo.OrderDeliveryDetail.DeliveredQuantity) AS DeliveredQuantity 
FROM dbo.OrderDeliveryDetail 
INNER JOIN dbo.Customers ON dbo.OrderDeliveryDetail.BusinessUnitId = dbo.Customers.BusinessUnitId 
INNER JOIN dbo.Items ON dbo.OrderDeliveryDetail.BusinessUnitId = dbo.Items.BusinessUnitId AND dbo.OrderDeliveryDetail.ItemId = dbo.Items.ItemId 
GROUP BY dbo.Customers.Name, dbo.Items.Name, dbo.OrderDeliveryDetail.DeliveredQuantity 

我写此查询，让我执行查询时DeliveredQuantity的总和，但他并没有返回DeliveredQuantity的总和

Answer 1

dbo.OrderDeliveryDetail.DeliveredQuantity不应位于group by子句中，因为您正在对此变量进行求和。

所以尝试：

SELECT dbo.Customers.Name, dbo.Items.Name AS ItemName,  SUM(dbo.OrderDeliveryDetail.DeliveredQuantity) AS DeliveredQuantity 
FROM   dbo.OrderDeliveryDetail INNER JOIN 
dbo.Customers ON dbo.OrderDeliveryDetail.BusinessUnitId =dbo.Customers.BusinessUnitId INNER JOIN 
dbo.Items ON dbo.OrderDeliveryDetail.BusinessUnitId = dbo.Items.BusinessUnitId AND dbo.OrderDeliveryDetail.ItemId = dbo.Items.ItemId 
GROUP BY dbo.Customers.Name, dbo.Items.Name 

Answer 2

从GROUP BY取出DeliveredQuantity，因为它是一个集合列：

SELECT c.Name, 
     i.Name     AS ItemName, 
     SUM(d.DeliveredQuantity) AS DeliveredQuantity 
FROM dbo.OrderDeliveryDetail d 
     INNER JOIN dbo.Customers c 
       ON d.BusinessUnitId = c.BusinessUnitId 
     INNER JOIN dbo.Items i 
       ON d.BusinessUnitId = i.BusinessUnitId 
        AND d.ItemId = i.ItemId 
GROUP BY c.Name, 
      i.Name