您将
COUNT(DISTINCT ...)在其他SQL引擎中使用：
SELECt CID, Count(DISTINCT O.OrderID) AS TotalOrders, Sum(OI.Quantity*OI.SalePrice) AS TotalDollarAmount FROM [Order] OINNER JOIN [OrderItem] OI ON O.OrderID = OI.OrderIDGROUP BY CID Order BY Count(DISTINCT O.OrderID) DESC
不幸的是，哪个Access不支持。相反，您可以先获取订单金额，然后在确定订单数量之前将它们加入：
SELECt CID, COUNT(Orders.OrderID) AS TotalOrders, SUM(OrderAmounts.DollarAmount) AS TotalDollarAmountFROM [Orders]INNER JOIN (SELECt OrderID, Sum(Quantity*SalePrice) AS DollarAmount FROM OrderItems GROUP BY OrderID) AS OrderAmounts ON Orders.OrderID = OrderAmounts.OrderIDGROUP BY CIDORDER BY Count(Orders.OrderID) DESC
如果您需要包括订单中没有商品的客户（非正常但可能），请更改
INNER JOIN为
LEFT OUTER JOIN。
