根据VBA SQL出售的数量，从每个子类别的每个子类别中找到平均收入

Question

我有两个数据集prod_cat_info和Transactions，其中两个用于产品类别和子类别的公共字段。我试图从销售的数量方面，通过前5个类别的每个子类别获得平均收入。我从以下查询中尝试了该子句不起作用的地方。

SELECT prod_cat_info.prod_subcat, AVG(Transactions.total_amt)
FROM Transactions INNER JOIN
    prod_cat_info
    ON Transactions.prod_cat_code = prod_cat_info.prod_cat_code AND
        Transactions.prod_subcat_code = prod_cat_info.prod_sub_cat_code
GROUP BY prod_cat_info.prod_subcat
WHERE Transactions.prod_cat_code IN (
    SELECT TOP 5 Transactions.prod_cat_code
    FROM Transactions
    GROUP BY Transactions.prod_cat_code
    ORDER BY SUM(Transactions.Qty) DESC
    )
GROUP BY prod_cat_info.prod_subcat

Answer 1

您根本不需要子查询。只需使用TOP和ORDER BY：

SELECT TOP (5) pci.prod_subcat, AVG(t.total_amt)
FROM Transactions t INNER JOIN
     prod_cat_info pci
     ON t.prod_cat_code = pci.prod_cat_code AND
        t.prod_subcat_code = pci.prod_sub_cat_code
GROUP BY pci.prod_subcat 
ORDER BY SUM(t.qty DESC);

编辑：

在MS Access和澄清的问题中：

SELECT pci.prod_cat_code, pci.prod_subcat, AVG(t.total_amt)
FROM Transactions as t INNER JOIN
     prod_cat_info as pci
     ON t.prod_cat_code = pci.prod_cat_code AND
        t.prod_subcat_code = pci.prod_sub_cat_code
WHERE pci.prod_cat_code IN (
          SELECT TOP 5 t.prod_cat_code
          FROM Transactions as t
          GROUP BY t.prod_cat_code
          ORDER BY t.qty DESC
         )
GROUP BY pci.prod_cat_code, pci.prod_subcat;