我有两张桌子Order
与列:
OrderID,OrderDate,CID,EmployeeID
And OrderItem
与列:
OrderID,ItemID,Quantity,SalePrice
我需要返回客户 ID(CID)、每个客户的订单数以及每个客户所有订单的总金额。
到目前为止,我有两个单独的查询。其中一个给我客户订单的数量......
SELECT CID, Count(Order.OrderID) AS TotalOrders
FROM [Order]
Where CID = CID
GROUP BY CID
Order BY Count(Order.OrderID) DESC;
另一个给我总销售额。我在组合它们时遇到了麻烦......
SELECT CID, Sum(OrderItem.Quantity*OrderItem.SalePrice) AS TotalDollarAmount
FROM OrderItem, [Order]
WHERE OrderItem.OrderID = [Order].OrderID
GROUP BY CID
我在 Access 2010 中执行此操作。
你会使用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
遗憾的是 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
如果您需要包括订单中不含商品的客户(不常见但可能),请更改INNER JOIN
to LEFT OUTER JOIN
.
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)