这个sql语句什么问题？

tangjinyi

SELECT dbo.Orders.Id, dbo.Orders.OrderDate, dbo.Orders.Journal, dbo.Orders.Billing,
      Billing.Name AS BillingName, Billing.Email AS BillingEmail,
      Billing.Address + ',' + Billing.Region AS BillingAddress, Billing.Zip AS BillingZip,
      Billing.Phone AS BillingPhone, dbo.Orders.Shipping,
      Shipping.Name AS ShippingName, Shipping.Email AS ShippingEmail,
      Shipping.Address + ',' + Shipping.Region AS ShippingAddress,
      Shipping.Zip AS ShippingZip, Shipping.Phone AS ShippingPhone,
      dbo.Orders.Payment, dbo.Payment.Holder, dbo.Payment.Credit, dbo.Payment.Expire,
      COUNT(dbo.Details.ProductId) AS Quantity, SUM(dbo.Product.Price) AS Total,
      dbo.Orders.Status
FROM dbo.Orders INNER JOIN
      dbo.Details ON dbo.Orders.Id = dbo.Details.OrderId INNER JOIN
      dbo.Contact AS Shipping ON dbo.Orders.Shipping = Shipping.Id INNER JOIN
      dbo.Contact AS Billing ON dbo.Orders.Billing = Billing.Id INNER JOIN
      dbo.Product ON dbo.Details.ProductId = dbo.Product.Id INNER JOIN
      dbo.Payment ON dbo.Orders.Payment = dbo.Payment.Id
GROUP BY dbo.Orders.Id, dbo.Orders.OrderDate, dbo.Orders.Journal, dbo.Orders.Billing,
      Billing.Name, Billing.Email, Billing.Address, Billing.Region, Billing.Zip, Billing.Phone,
      dbo.Orders.Shipping, Shipping.Name, Shipping.Email, Shipping.Address,
      Shipping.Region, Shipping.Zip, Shipping.Phone, dbo.Orders.Payment,
      dbo.Payment.Holder, dbo.Payment.Credit, dbo.Payment.Expire, dbo.Orders.Status


一个产品订单。

订单表Orders表，里面有Billing和Shiping、Payment字段，记录付款联系信息、越洋邮寄地址信息。另外一个Details表，记录详细订单信息，包括对应的OrderId,以及产品编号ProductId，这样就可以用COUNT(dbo.Details.ProductId) AS Quantity, SUM(dbo.Product.Price) AS Total, 分别统计出订单包含的产品总数和总金额。问题是为什么统计不出来总金额？

我是杀人狂

那么多表联接,看着眼花了.

朗基奴斯

眼睛都花了

tangjinyi

从四个表里查询，订单表是Orders , 辅助表Details记录的是所有选定的产品，记录ProductId，已经对应订单的OrderId,不能根据ProductId查询出总价格 ？

wgfxman

dbo.Contact AS Shipping ON dbo.Orders.Shipping = Shipping.Id INNER JOIN
dbo.Contact AS Billing ON dbo.Orders.Billing = Billing.Id INNER JOIN

这样用的少见~。。常见的是下面这个
INNER JOIN dbo.Contact ON Orders.Shipping = Contact.Id AND Orders.Billing = Contact.Id

还有，看看NULL值转成0还是...

tangjinyi

这个不是主要原因吧？
关键是COUNT(dbo.Details.ProductId) AS Quantity, SUM(dbo.Product.Price) AS Total,
这个问题，我把SUM(dbo.Product.Price) AS Total去掉就可以了，但是没有总额这个列了

wgfxman

统计不出来总金额？

是结果数字不对，还是报错？
。。。

gaoqingfeng

楼主太强了，我第一次看到group by 后面跟这么多字段的，长见识了！