MS Access SQL如何按餐厅分组查询各门店订单量前5的餐品

问题原因分析 #

1. 第一版代码的问题：子查询直接取了全局的前5个餐品，没有按餐厅维度做区分，所以所有餐厅返回的都是相同的5款餐品。
2. 第二版修改后代码的问题：子查询的过滤条件写了p2.ItemName = Products.ItemName，相当于每个餐品只和自身对比，必然满足TOP 5要求，所以会返回全量餐品；同时子查询仍然没有关联餐厅维度，没有实现「按餐厅单独算排名」的逻辑。另外你写的SUM(Quantity)*COUNT(Orders.OrderNumber)计算逻辑错误，销量总数直接用SUM(OrderDetails.Quantity)就可以统计所有订单里该餐品的售卖总份数。

正确实现思路 #

MS Access 不支持ROW_NUMBER()这类窗口函数，所以要通过关联子查询的方式，对每个餐厅下的每个餐品，统计有多少个同餐厅的餐品销量比它高，当这个数量小于5时，就是该餐厅的前5名餐品。

可运行的SQL代码 #

SELECT 
  t1.RestaurantName, 
  t1.ItemName, 
  t1.NumberofOrders
FROM (
  -- 先计算每个餐厅每个餐品的总销量
  SELECT 
    Restaurants.RestaurantName, 
    Products.ItemName, 
    SUM(OrderDetails.Quantity) AS NumberofOrders
    -- 如果需要统计下单次数而非售卖份数，可以替换成下方写法（MS Access不支持COUNT DISTINCT，所以用子查询实现）
    -- (SELECT COUNT(*) FROM Orders o WHERE o.RestID = Restaurants.RestID AND o.OrderNumber IN (SELECT OrderNumber FROM OrderDetails od WHERE od.ItemID=Products.ItemID)) AS NumberofOrders
  FROM Restaurants
  INNER JOIN Orders ON Restaurants.RestID = Orders.RestID
  INNER JOIN OrderDetails ON Orders.OrderNumber = OrderDetails.OrderNumber
  INNER JOIN Products ON OrderDetails.ItemID = Products.ItemID
  -- 如果你不需要用到FoodType的字段，这个JOIN可以直接删除
  INNER JOIN FoodType ON Products.ProdTypeID = FoodType.ProdType
  GROUP BY Restaurants.RestaurantName, Products.ItemName
) AS t1
WHERE (
  -- 统计同餐厅下，销量比当前餐品高的餐品数量，小于5即为前5名
  SELECT COUNT(*)
  FROM (
    SELECT 
      Restaurants.RestaurantName, 
      Products.ItemName, 
      SUM(OrderDetails.Quantity) AS SaleNum
    FROM Restaurants
    INNER JOIN Orders ON Restaurants.RestID = Orders.RestID
    INNER JOIN OrderDetails ON Orders.OrderNumber = OrderDetails.OrderNumber
    INNER JOIN Products ON OrderDetails.ItemID = Products.ItemID
    INNER JOIN FoodType ON Products.ProdTypeID = FoodType.ProdType
    GROUP BY Restaurants.RestaurantName, Products.ItemName
  ) AS t2
  WHERE t2.RestaurantName = t1.RestaurantName 
  AND t2.SaleNum > t1.NumberofOrders
) < 5
ORDER BY t1.RestaurantName, t1.NumberofOrders DESC;

特殊说明 #

如果存在同餐厅多个餐品销量并列第5的情况，查询会把所有并列的餐品都返回。如果需要严格只返回5个，可以把子查询里的<5改成<=4，或者调整子查询的排序逻辑，加上ItemName作为次要排序条件。

内容的提问来源于stack exchange，提问作者Beeeeee