如何在含子查询的计算中正确使用ISNULL函数获取预期库存余额

你的问题核心是ISNULL的作用范围错误：当前ISNULL包裹了整个子查询，只有当子查询完全无返回结果时才会替换为0；但实际场景中，当没有匹配的Item Ledger Entry记录时，SUM(ILE.[Remaining Quantity])会返回NULL，导致NULL - CTE.[ExtendedQuantity]的结果仍是NULL，外层ISNULL直接将这个NULL转为0，最终得到错误的0而非预期的负数。

方案1：调整ISNULL到SUM函数外层（推荐） #

将ISNULL移到子查询内部，仅处理SUM(...)的NULL值，再执行减法逻辑：

(
    SELECT
        ISNULL(SUM(ILE.[Remaining Quantity]), 0) - CTE.[ExtendedQuantity] 
    FROM [Item Ledger Entry] ILE
    GROUP BY ILE.[Item No_]
    HAVING (CTE.[ItemNo] = ILE.[Item No_])
) AS [Balance]

这样即使没有匹配的ledger记录，SUM(...)会被转为0，再减去ExtendedQuantity就能得到正确的负数结果（比如0-8=-8）。如果需要处理子查询完全无返回的极端情况，可以在外层补充逻辑：

ISNULL(
    (
        SELECT
            ISNULL(SUM(ILE.[Remaining Quantity]), 0) - CTE.[ExtendedQuantity] 
        FROM [Item Ledger Entry] ILE
        GROUP BY ILE.[Item No_]
        HAVING (CTE.[ItemNo] = ILE.[Item No_])
    ), 0 - CTE.[ExtendedQuantity]
) AS [Balance]

方案2：使用CASE表达式实现 #

如果你倾向于用CASE逻辑，本质和方案1一致，写法上更直观：

(
    SELECT
        CASE 
            WHEN SUM(ILE.[Remaining Quantity]) IS NULL THEN 0 
            ELSE SUM(ILE.[Remaining Quantity]) 
        END - CTE.[ExtendedQuantity]
    FROM [Item Ledger Entry] ILE
    GROUP BY ILE.[Item No_]
    HAVING (CTE.[ItemNo] = ILE.[Item No_])
) AS [Balance]

方案3：改用LEFT JOIN优化性能（可选） #

如果数据量较大，标量子查询可能存在性能瓶颈，改用LEFT JOIN的写法更高效且逻辑清晰：

SELECT
    CTE.[ItemNo] AS [Item No.],
    ISNULL(ILE_Total.TotalRemaining, 0) AS [Qty. In Stock],
    CTE.[ExtendedQuantity] AS [Qty. Needed],
    ISNULL(ILE_Total.TotalRemaining, 0) - CTE.[ExtendedQuantity] AS [Balance]
FROM CTE
LEFT JOIN (
    SELECT 
        [Item No_], 
        SUM([Remaining Quantity]) AS TotalRemaining
    FROM [Item Ledger Entry]
    GROUP BY [Item No_]
) ILE_Total ON CTE.[ItemNo] = ILE_Total.[Item No_]

调整后，目标数据的结果将符合预期：

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