基于四列主键的出入库SQL计算QTY_STOCK结果异常求助

问题根源 #

原SQL存在两个核心问题导致库存计算结果不符：

1. 重复匹配导致计算错误：如果StocksoutDetail中同一CodeProduct/Barcode/Colorcode/Size组合存在多条出库记录，LEFT JOIN会让StocksinDetail的对应记录被重复关联，最终t1.Qty - t2.Qty会错误地多次扣除出库量。
2. NULL值处理缺失：当某产品无对应出库记录时，t2.Qty为NULL，t1.Qty - NULL会返回NULL，而非预期的t1.Qty（即库存等于入库量）。

修正后的SQL（适配VB.Net，无Access专属函数） #

场景1：StocksinDetail中同一产品仅一条入库记录 #

SELECT  
    t1.CodeProduct, t1.Barcode, t1.Colorcode, t1.Size, 
    t1.ColorName,
    (t1.Qty - CASE WHEN t2.TotalOutQty IS NULL THEN 0 ELSE t2.TotalOutQty END) AS QTY_STOCK, 
    t1.Qty AS QTY_STOCKIN, 
    CASE WHEN t2.TotalOutQty IS NULL THEN 0 ELSE t2.TotalOutQty END AS QTY_STOCKOUT
FROM
    StocksinDetail AS t1
LEFT JOIN 
    (
        -- 先按主键分组汇总出库总量，避免重复匹配
        SELECT CodeProduct, Barcode, Colorcode, Size, SUM(Qty) AS TotalOutQty
        FROM StocksoutDetail
        GROUP BY CodeProduct, Barcode, Colorcode, Size
    ) AS t2 ON t1.CodeProduct = t2.CodeProduct
           AND t1.Barcode = t2.Barcode
           AND t1.Colorcode = t2.Colorcode
           AND t1.Size = t2.Size;

场景2：StocksinDetail中同一产品存在多条入库记录 #

如果入库表也有重复记录，需先汇总入库总量，再关联出库汇总数据：

SELECT  
    t1.CodeProduct, t1.Barcode, t1.Colorcode, t1.Size, 
    t1.ColorName,
    (t1.TotalInQty - CASE WHEN t2.TotalOutQty IS NULL THEN 0 ELSE t2.TotalOutQty END) AS QTY_STOCK, 
    t1.TotalInQty AS QTY_STOCKIN, 
    CASE WHEN t2.TotalOutQty IS NULL THEN 0 ELSE t2.TotalOutQty END AS QTY_STOCKOUT
FROM
    (
        -- 汇总入库总量
        SELECT CodeProduct, Barcode, Colorcode, Size, ColorName, SUM(Qty) AS TotalInQty
        FROM StocksinDetail
        GROUP BY CodeProduct, Barcode, Colorcode, Size, ColorName
    ) AS t1
LEFT JOIN 
    (
        -- 汇总出库总量
        SELECT CodeProduct, Barcode, Colorcode, Size, SUM(Qty) AS TotalOutQty
        FROM StocksoutDetail
        GROUP BY CodeProduct, Barcode, Colorcode, Size
    ) AS t2 ON t1.CodeProduct = t2.CodeProduct
           AND t1.Barcode = t2.Barcode
           AND t1.Colorcode = t2.Colorcode
           AND t1.Size = t2.Size;

方案说明 #

• 采用分组汇总子查询处理出库（及入库）表的重复记录，确保每个主键组合仅对应一条汇总数据，避免重复关联导致的计算错误。
• 使用标准SQL的CASE WHEN替代Access专属函数处理NULL值，将无出库记录的情况转为0，保证库存计算逻辑符合预期。
• 语法完全兼容VB.Net的OleDb/ODBC数据库访问场景，无需依赖Access内置函数。

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