从多个表访问查询

Question

我目前有一个Access数据库，它有几个表，其中包括Order、OrderDetails和Client。

• 顺序(OrderID，TimeStamp，FKEmployeeID，FKClientID，OrderStatus，注释)
• OrderDetails (OrderDetailsID，FKOrderID，FKProductID，数量，成本，总成本)
• 客户端(ClientID，名称)

我正在尝试构建一个查询，在这里我可以获得客户端的总订单和总项目。

示例：

Customer,  Total Orders,  Total Items
John,          5,              15
Alex,          2,              30
Ana,           1,              3

每当我试图进行查询时，总订单和总项目都给我相同的编号。

任何帮助都将不胜感激！

Answer 1

即使ms access也支持这一点：

SELECT c.Name,  
(select count(*) 
 from Orders o 
 where o. FKClientID = C.ClientID) as [Total Orders],
(select sum(Quantity) as Items 
 from OrderDetails od 
 inner join Order o on o.OrderID = od.FKOrderID
 where o.FKClientId = C.ClientID) as [Total Items]
from Client c;

Answer 2

这个解决方案怎么样(在此，没有订单的客户将被排除在外)：

SELECT Client.[Name], 
       Count(myTotalItems.OrderID) As TotalOrders, 
       Sum(myTotalItems.TotalItems) As TotalItems
FROM Client,
    (SELECT First([Order].OrderID) As OrderID, 
            First([Order].FKClientID) As 
            ClientID, 
            Count(OrderDetails.OrderDetailsID) As TotalItems 
     FROM [Order], OrderDetails 
     WHERE OrderDetails.FKOrderID like [Order].OrderID
     GROUP BY [Order].OrderID) As myTotalItems 
WHERE myTotalItems.ClientID like Client.ClientID 
GROUP BY Client.[Name];

Answer 3

不幸的是，MS不支持COUNT(DISTINCT)。您可以通过两个聚合来完成这一任务：

SELECT c.[Name], COUNT(*) As NumOrders, SUM(o.NumItems) As NumItems
FROM Client as c INNER JOIN
     (SELECT o.OrderID, o.FKClientID, COUNT(*) As NumItems 
      FROM [Order] as o INNER JOIN
           OrderDetails as od
           ON od.FKOrderID = o.OrderID
      GROUP BY o.OrderID, o.FKClientID
     ) as o
     ON o.FKClientID = c.ClientId
GROUP BY c.ClientId, c.Name;