如何在Access ODBC的SQL GROUP BY查询中添加衍生计算列？

CTE的可用性说明 #

Microsoft Access SQL（包括ODBC连接场景）不支持CTE（WITH子句），因此无法通过CTE实现该需求。

替代实现方案 #

方案1：嵌套子查询（无需保存中间查询） #

将原聚合查询作为子查询嵌套在外层SELECT中，直接在外部计算新增字段，无需提前保存中间查询：

SELECT 
    sub.MasterItemID,
    sub.ItemID,
    sub.orderedByZG,
    sub.orderedFromZG,
    sub.returnedItems,
    sub.inventoryAdjustments,
    -- 计算当前库存
    sub.orderedByZG - sub.orderedFromZG - sub.returnedItems + sub.inventoryAdjustments AS quantityOnHand,
    -- 计算退货百分比（处理除数为0的情况，避免报错）
    IIF(sub.orderedFromZG = 0, 0, sub.returnedItems / sub.orderedFromZG * 100) AS returnPercentage
FROM (
    -- 原聚合逻辑作为子查询
    SELECT 
        MasterItemID, ItemID,
        SUM(SWITCH(JournalEX = 18, StockQtyRec, JournalEX <> 18, 0)) AS orderedByZG,
        SUM(SWITCH(JournalEX = 19, StockQty, JournalEX <> 19, 0)) AS orderedFromZG,
        ABS(SUM(SWITCH(JournalEX = 9 AND IncludeInInvLedger = 1, StockQty, JournalEX <> 9 AND IncludeInInvLedger <> 1, 0))) AS returnedItems,
        SUM(SWITCH(JournalEX = 14 AND IncludeInInvLedger = 1, StockQty, JournalEX <> 14 AND IncludeInInvLedger <> 1, 0)) AS inventoryAdjustments
    FROM 
        qryHdrtoLineItem
    GROUP BY 
        MasterItemID, ItemID
) AS sub;

方案2：保留当前保存中间查询的方式 #

如果觉得嵌套子查询可读性不足，继续使用保存中间查询再引用的方式完全可行，Access会对这类查询做优化，性能上与嵌套子查询无明显差距。

关键注意点 #

• 计算returnPercentage时必须处理orderedFromZG = 0的场景，否则会触发除以零的错误，示例中用IIF函数判断后返回0，你可根据业务需求调整为NULL或其他值。
• Access中的SWITCH函数功能与标准SQL的CASE等效，你当前的聚合逻辑是正确的。

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