请求协助修正MS Access SQL语法错误及潜在问题

错误根源分析 #

1. 语法错误：WHERE子句中给IN子查询添加别名subQryCount属于语法错误，IN子查询不能直接附加别名，且你的逻辑需要引用统计值参与计算，应将统计子查询作为派生表通过JOIN引入。
2. 逻辑错误：原WHERE条件用IN匹配COUNT结果无意义——COUNT返回单一数值，且核心需求是用该统计值计算，因此必须将统计子查询转为JOIN关联的派生表。
3. 语法不规范：混用中文单引号‘和英文单引号'，GROUP BY未包含所有非聚合字段（不符合SQL标准）。

修正后的代码 #

SELECT 
    Inventory.Item_No, 
    Inventory.NDC, 
    Pharmacy.NDC_Desc, 
    ROUND(Inventory.OH_Qty_EA / Pharmacy.McK_PackQty, 0) AS 'McKesson_OH_Qty',
    SUM(Purchases.SUOM_Orders / subQryCount.Total_Orders) * Pharmacy.Safecor_PackQty * 1.5 / Pharmacy.McK_PackQty AS 'Avg_Par', 
    SUM(Purchases.SUOM_Orders / subQryCount.Total_Orders) * Pharmacy.Safecor_PackQty * 1.5 - (Inventory.OH_Qty_EA / Pharmacy.McK_PackQty) AS 'Avg_OrdQty'
FROM 
    Inventory 
LEFT JOIN 
    Pharmacy ON Inventory.Item_No = Pharmacy.Item_No
LEFT JOIN 
    Purchases ON Pharmacy.Item_No = Purchases.Item_No
LEFT JOIN (
    -- 统计近120天所有订单的总数（若需按商品单独统计，需添加GROUP BY Purchases.Item_No）
    SELECT COUNT(SUOM_Orders) AS Total_Orders
    FROM Purchases
    WHERE Order_Date >= DATE() - 120
) AS subQryCount ON 1=1 -- 全局统计用恒真条件关联
WHERE 
    Purchases.SUOM_Orders IS NOT NULL -- 可选：过滤无采购记录的行
GROUP BY 
    Inventory.Item_No, 
    Inventory.NDC, 
    Pharmacy.NDC_Desc, 
    Inventory.OH_Qty_EA, 
    Pharmacy.McK_PackQty, 
    Pharmacy.Safecor_PackQty
HAVING 
    Avg_OrdQty > 0;

关键调整说明 #

• 将原WHERE中的统计子查询转为LEFT JOIN的派生表subQryCount，给统计字段命名为Total_Orders以便引用。
• 统一使用英文单引号包裹别名，避免语法解析错误。
• GROUP BY子句添加所有非聚合字段，符合SQL标准，避免数据库兼容性问题。
• 若需按每个商品统计近120天的订单数，而非全局总数，修改派生表为：

SELECT Item_No, COUNT(SUOM_Orders) AS Total_Orders
FROM Purchases
WHERE Order_Date >= DATE() - 120
GROUP BY Item_No

同时将JOIN条件改为ON Purchases.Item_No = subQryCount.Item_No。

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