mysql - sql查询计算订单和数量

Question

Stack Overflow 社区下午好!我对 sql 和编写查询还很陌生，所以如果有人可以仔细检查我的工作，并可能传递一些有用的提示，告诉我如何更好地编码呢？我已经粘贴了查询的目的和我的代码 - 我感谢您的帮助和反馈，谢谢!

基本上，查询应该创建一份居住在欧洲的客户的报告，这些客户最近下的订单总额超过 5,000 美元。订单总额必须反射(reflect)客户订购的产品数量，还应该有折扣和订单总额。

再次感谢您的帮助!

select c.CustomerID, c.Country, sum (od.Discount * od.UnitPrice)'Total'
from Customers c
inner join Orders o
on c.CustomerID = o.CustomerID
inner join OrderDetails od
on o.OrderID = od.OrderID
where c.Country in ('germany', 'UK', 'sweden', 'france', 'spain', 'switzerland','austria', 'italy', 'belgium', 'norway', 'denmark', 'finland', 'poland') 
group by c.Country, c.CustomerID
having sum ('Total' * od.Quantity) <= 5000
order by total desc

Answer 1

看起来不错，但有几点:1) 您的总计可能有误2) 必须反转比较运算符:

select c.CustomerID, c.Country, sum (od.Discount * od.UnitPrice * od.Quantity) 'Total'
from Customers c
inner join Orders o
on c.CustomerID = o.CustomerID
inner join OrderDetails od
on o.OrderID = od.OrderID
where c.Country in ('germany', 'UK', 'sweden', 'france', 'spain', 
  'switzerland','austria', 'italy', 'belgium', 'norway', 'denmark', 
  'finland', 'poland') 
and o.OrderDate >= '2017-03-01' -- for example
group by c.Country, c.CustomerID
having sum ( od.Discount * od.UnitPrice * od.Quantity) >= 5000
order by total desc