如何去除SQL查询中Receipt Qty列的重复值？

当前SQL查询结果中的Receipt Qty列存在重复值，请问如何去除该列的重复值？

原查询代码：

select DISTINCT T1."ItemCode" ,T1."ItemName",T2."WhsCode",

(Select IFNULL((Sum("InQty"-"OutQty")),0) from OINM where "DocDate" <[%0] and "ItemCode"=T0."ItemCode" and "Warehouse" = T2."WhsCode") as "Opening Qty",

(select IFNULL ((Sum("Quantity")),0) From PDN1 where "DocDate">=[%0] and "DocDate"<=[%1] and "ItemCode"=T0."ItemCode" and "Warehouse" = T2."WhsCode" AND "DocEntry" = T3."DocEntry" AND T3."CANCELED"= 'N') as "Receipt Qty",

((select IFNULL((Sum("OutQty")),0) From OINM where "DocDate">=[%0] and "DocDate"<=[%1] and "ItemCode"=T0."ItemCode" and "Warehouse" = T2."WhsCode" and "TransType"='67')) as "Issue Qty",

((Select IFNULL((Sum("InQty"-"OutQty")),0) from OINM where "DocDate" < [%0] and "ItemCode"=T0."ItemCode" and "Warehouse" = T2."WhsCode") +

(select IFNULL ((Sum("Quantity")),0) From PDN1 where "DocDate">=[%0] and "DocDate"<=[%1] and "ItemCode"=T0."ItemCode" and "Warehouse" = T2."WhsCode" AND "DocEntry" = T3."DocEntry" AND T3."CANCELED"= 'N') - ((select IFNULL((Sum("OutQty")),0) From OINM where "DocDate">=[%0] and "DocDate"<=[%1] and "ItemCode"=T0."ItemCode" and "Warehouse" = T2."WhsCode" and "TransType"='67'))) as "Closing Balance"

FROM OINM T0 INNER JOIN OITM T1 ON T0."ItemCode" = T1."ItemCode"

FULL JOIN OPDN T3 ON T0."CardCode" = T3."CardCode" AND T0."DocDate" = T3."DocDate"

FULL JOIN PDN1 T4 ON T0."ItemCode" = T4."ItemCode" AND T0."InQty" = T4."Quantity"

LEFT JOIN OWHS T2 ON T0."Warehouse" = T2."WhsCode"

WHERE T2."WhsCode" = '[%2]'

Group by T1."ItemCode",T1."ItemName",T2."WhsCode",T0."ItemCode",T0."Warehouse", T3."DocEntry", T3."CANCELED"

ORDER BY T1."ItemCode";

注：

/*select T0."DocDate" AS "DocDate" from OINM T0 where T0."DocDate">=[%0] and T0."DocDate"<=[%1] */

问题原因 #

重复值的核心诱因：

• 主查询用FULL JOIN关联OINM与入库单表（OPDN/PDN1），导致同一物料+仓库组合因多条入库单记录被拆分成多行；
• GROUP BY包含T3.DocEntry（入库单编号），强制相同物料+仓库的记录按不同入库单分组，直接导致Receipt Qty重复；
• Receipt Qty子查询关联主查询的T3.DocEntry，但OINM可能与同一张入库单有多条关联，引发重复计算。

解决方法 #

调整查询逻辑，以物料+仓库为核心统计维度，去掉冗余关联与分组字段，改写子查询避免重复计算：

修改后的SQL代码：

select 
    T1."ItemCode",
    T1."ItemName",
    T2."WhsCode",
    -- 期初库存：统计截止到[%0]之前的库存结余
    IFNULL((SELECT SUM("InQty"-"OutQty") FROM OINM WHERE "DocDate" < [%0] AND "ItemCode" = T1."ItemCode" AND "Warehouse" = T2."WhsCode"), 0) AS "Opening Qty",
    -- 入库数量：统计日期范围内的有效入库（未取消的PDN）
    IFNULL((SELECT SUM("Quantity") FROM PDN1 
            JOIN OPDN T3 ON PDN1."DocEntry" = T3."DocEntry"
            WHERE PDN1."DocDate" BETWEEN [%0] AND [%1] 
              AND PDN1."ItemCode" = T1."ItemCode" 
              AND PDN1."Warehouse" = T2."WhsCode" 
              AND T3."CANCELED" = 'N'), 0) AS "Receipt Qty",
    -- 出库数量：统计日期范围内的类型67出库
    IFNULL((SELECT SUM("OutQty") FROM OINM WHERE "DocDate" BETWEEN [%0] AND [%1] AND "ItemCode" = T1."ItemCode" AND "Warehouse" = T2."WhsCode" AND "TransType" = '67'), 0) AS "Issue Qty",
    -- 期末库存：期初+入库-出库
    (IFNULL((SELECT SUM("InQty"-"OutQty") FROM OINM WHERE "DocDate" < [%0] AND "ItemCode" = T1."ItemCode" AND "Warehouse" = T2."WhsCode"), 0) +
     IFNULL((SELECT SUM("Quantity") FROM PDN1 
             JOIN OPDN T3 ON PDN1."DocEntry" = T3."DocEntry"
             WHERE PDN1."DocDate" BETWEEN [%0] AND [%1] 
               AND PDN1."ItemCode" = T1."ItemCode" 
               AND PDN1."Warehouse" = T2."WhsCode" 
               AND T3."CANCELED" = 'N'), 0) -
     IFNULL((SELECT SUM("OutQty") FROM OINM WHERE "DocDate" BETWEEN [%0] AND [%1] AND "ItemCode" = T1."ItemCode" AND "Warehouse" = T2."WhsCode" AND "TransType" = '67'), 0)) AS "Closing Balance"
FROM OITM T1
-- 关联仓库表，锁定目标仓库
JOIN OWHS T2 ON T2."WhsCode" = '[%2]'
-- 关联OINM确保有库存记录（若需包含无库存物料，改为LEFT JOIN）
JOIN OINM T0 ON T0."ItemCode" = T1."ItemCode" AND T0."Warehouse" = T2."WhsCode"
-- 按核心维度分组，确保每个物料+仓库仅返回一条记录
GROUP BY T1."ItemCode", T1."ItemName", T2."WhsCode"
ORDER BY T1."ItemCode";

关键修改点 #

1. 移除冗余FULL JOIN：不在主查询关联入库单表，改为在Receipt Qty子查询内部关联，避免主查询结果被拆分；
2. 简化GROUP BY：仅保留ItemCode、ItemName、WhsCode三个统计维度，确保每个物料+仓库唯一；
3. 改写入库统计逻辑：子查询内直接关联PDN1与OPDN，按维度统计有效入库，规避主查询关联带来的重复；
4. 删除多余DISTINCT：GROUP BY已保证维度唯一性，DISTINCT无实际作用。

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