MS Access数据库LEFT JOIN查询结果不符的问题排查及VB.Net适配需求

我在MS Access数据库中执行LEFT JOIN的SQL查询后，结果与预期不符，怀疑是SQL代码存在问题。需要一个不依赖MS Access内置函数、可直接用于VB.Net的SQL解决方案。

涉及表结构 #

StocksinDetail（入库明细） #

StocksoutDetail（出库明细） #

当前查询返回的未合并结果 #

期望结果 #

当前使用的SQL代码 #

SELECT  
    t1.CodeProduct, t1.Barcode, t1.Colorcode, t1.Size, 
    t1.ColorName,
    IIf(IsNull(t1.Qty),'0',t1.Qty)-IIf(IsNull(t2.Qty),'0',t2.Qty) AS QTY_STOCK,
    t1.Qty AS QTY_STOCKIN, t2.Qty AS QTY_STOCKOUT
FROM
    StocksinDetail AS t1
LEFT JOIN 
    StocksoutDetail AS t2 ON (t1.Size = t2.Size)
                          AND (t1.Colorcode = t2.Colorcode)
                          AND (t1.Barcode = t2.Barcode)
                          AND (t1.CodeProduct = t2.CodeProduct);

问题原因分析 #

1. 未分组汇总入库数据：当前SQL直接关联明细，未将相同CodeProduct、Barcode、Colorcode、Size、Colorname的入库记录合并求和，导致重复行存在，无法得到合并后的总数量。
2. 出库数量处理逻辑错误：如果同一分组有多条出库记录，明细关联会产生笛卡尔积，导致重复扣除出库数量；同时使用了Access专属的IIf和IsNull函数，无法直接在VB.Net的通用SQL环境中使用。

VB.Net兼容的SQL解决方案 #

先分别汇总入库和出库的分组数据，再进行LEFT JOIN关联计算，使用SQL标准函数替代Access专属函数：

SELECT
    in_sum.CodeProduct,
    in_sum.Barcode,
    in_sum.Colorcode,
    in_sum.Size,
    in_sum.Colorname,
    (in_sum.TotalIn - COALESCE(out_sum.TotalOut, 0)) AS Qty,
    in_sum.TotalIn AS QTY_STOCKIN,
    COALESCE(out_sum.TotalOut, 0) AS QTY_STOCKOUT
FROM
    (
        SELECT
            CodeProduct,
            Barcode,
            Colorcode,
            Size,
            Colorname,
            SUM(Qty) AS TotalIn
        FROM StocksinDetail
        GROUP BY CodeProduct, Barcode, Colorcode, Size, Colorname
    ) AS in_sum
LEFT JOIN
    (
        SELECT
            CodeProduct,
            Barcode,
            Colorcode,
            Size,
            Colorname,
            SUM(Qty) AS TotalOut
        FROM StocksoutDetail
        GROUP BY CodeProduct, Barcode, Colorcode, Size, Colorname
    ) AS out_sum
ON
    in_sum.CodeProduct = out_sum.CodeProduct
    AND in_sum.Barcode = out_sum.Barcode
    AND in_sum.Colorcode = out_sum.Colorcode
    AND in_sum.Size = out_sum.Size
    AND in_sum.Colorname = out_sum.Colorname;

方案说明 #

• 分组汇总：对入库、出库明细按商品唯一标识分组求和，得到每个分组的总入库、总出库数量，避免明细关联产生的重复行。
• 标准函数替代：使用COALESCE函数处理NULL值，当出库分组不存在时自动将出库数量视为0，这是SQL标准函数，可直接在VB.Net的ADO.NET环境中使用。
• 正确关联计算：基于汇总后的分组数据进行LEFT JOIN，确保每个分组只计算一次入库减出库的结果，符合预期逻辑。

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