用于计算每个客户的订单数量和总金额的 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13808069/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Query for counting number of orders per customer and Total Dollar amount
提问by user1892665
I have two tables
Orderwith columns:
我有两个
Order带列的表：
OrderID,OrderDate,CID,EmployeeID
And OrderItemwith columns:
并OrderItem带有列：
OrderID,ItemID,Quantity,SalePrice
I need to return the CustomerID(CID), number of orders per customer, and each customers total amount for all orders.
我需要返回 CustomerID(CID)、每个客户的订单数量以及每个客户所有订单的总金额。
So far I have two separate queries. One gives me the count of customer orders....
到目前为止，我有两个单独的查询。一个给我客户订单的数量....
SELECT CID, Count(Order.OrderID) AS TotalOrders
FROM [Order]
Where CID = CID
GROUP BY CID
Order BY Count(Order.OrderID) DESC;
And the other gives me the total sales. I'm having trouble combining them...
另一个给我总销售额。我在组合它们时遇到了麻烦...
SELECT CID, Sum(OrderItem.Quantity*OrderItem.SalePrice) AS TotalDollarAmount
FROM OrderItem, [Order]
WHERE OrderItem.OrderID = [Order].OrderID
GROUP BY CID
I'm doing this in Access 2010.
我在 Access 2010 中这样做。
采纳答案by PinnyM
You would use COUNT(DISTINCT ...)in other SQL engines:
您将COUNT(DISTINCT ...)在其他 SQL 引擎中使用：
SELECT CID,
Count(DISTINCT O.OrderID) AS TotalOrders,
Sum(OI.Quantity*OI.SalePrice) AS TotalDollarAmount
FROM [Order] O
INNER JOIN [OrderItem] OI
ON O.OrderID = OI.OrderID
GROUP BY CID
Order BY Count(DISTINCT O.OrderID) DESC
Which Access unfortunately does not support. Instead you can first get the Order dollar amounts and then join them before figuring the order counts:
不幸的是，Access 不支持。相反，您可以先获取订单金额，然后在计算订单数量之前加入它们：
SELECT CID,
COUNT(Orders.OrderID) AS TotalOrders,
SUM(OrderAmounts.DollarAmount) AS TotalDollarAmount
FROM [Orders]
INNER JOIN (SELECT OrderID, Sum(Quantity*SalePrice) AS DollarAmount
FROM OrderItems GROUP BY OrderID) AS OrderAmounts
ON Orders.OrderID = OrderAmounts.OrderID
GROUP BY CID
ORDER BY Count(Orders.OrderID) DESC
If you need to include Customers that have orders with no items (unusual but possible), change INNER JOINto LEFT OUTER JOIN.
如果您需要包括订单中没有商品的客户（不寻常但可能），请更改INNER JOIN为LEFT OUTER JOIN。
回答by HansUp
Create a query which uses your 2 existing queries as subqueriers, and join the 2 subqueries on CID. Define your ORDER BYin the parent query instead of in a subquery.
创建一个使用 2 个现有查询作为子查询的查询，并在 上加入 2 个子查询CID。ORDER BY在父查询中而不是在子查询中定义您的。
SELECT
sub1.CID,
sub1.TotalOrders,
sub2.TotalDollarAmount
FROM
(
SELECT
CID,
Count(Order.OrderID) AS TotalOrders
FROM [Order]
GROUP BY CID
) AS sub1
INNER JOIN
(
SELECT
CID,
Sum(OrderItem.Quantity*OrderItem.SalePrice)
AS TotalDollarAmount
FROM OrderItem INNER JOIN [Order]
ON OrderItem.OrderID = [Order].OrderID
GROUP BY CID
) AS sub2
ON sub1.CID = sub2.CID
ORDER BY sub1.TotalOrders DESC;
