SSMS中SQL多表JOIN使用SUM时统计数量异常问题

问题表现 #

• 原本可返回正确统计数量的三表关联查询，新增关联PART_LOCATION表获取状态字段后，SUM聚合计算的QTY值约为正确值的9倍，出现异常放大。

原正确查询（返回准确QTY值） #

该查询关联TRACE_INV_TRANS（别名TIT）、INVENTORY_TRANS（别名IT）、PART_SITE（别名P）三张表，筛选IT.LOCATION_ID='DISPATCH'、TIT.QTY非空的记录，按指定字段分组后筛选SUM(TIT.QTY)>0的结果，执行后数量统计正确，代码如下：

SELECT
  TIT.PART_ID,
  TIT.TRACE_ID,
  IT.WAREHOUSE_ID,
  IT.LOCATION_ID,
  P.PRIMARY_WHS_ID,
  P.PRIMARY_LOC_ID,
  P.BACKFLUSH_WHS_ID,
  P.BACKFLUSH_LOC_ID,
  P.AUTO_BACKFLUSH,
  SUM(TIT.QTY) AS QTY
FROM
TRACE_INV_TRANS TIT --TIT = TRACE INV TRANS TABLE
INNER JOIN INVENTORY_TRANS IT ON TIT.PART_ID = IT.PART_ID AND TIT.TRANSACTION_ID = IT.TRANSACTION_ID
LEFT JOIN PART_SITE P ON P.PART_ID = TIT.PART_ID
WHERE
  IT.LOCATION_ID = 'DISPATCH'
  AND TIT.QTY IS NOT NULL
GROUP BY
  TIT.TRACE_ID,
  TIT.PART_ID,
  IT.WAREHOUSE_ID,
  IT.LOCATION_ID,
  P.PRIMARY_WHS_ID,
  P.PRIMARY_LOC_ID,
  P.BACKFLUSH_WHS_ID,
  P.BACKFLUSH_LOC_ID,
  P.AUTO_BACKFLUSH
HAVING
  SUM(TIT.QTY) > 0

异常修改查询 #

需求为新增关联PART_LOCATION（别名L）表获取STATUS字段，要求关联后QTY统计值与原结果一致。修改后代码新增了L.STATUS查询字段、PART_LOCATION表关联逻辑、L.STATUS='A'筛选条件，同时将L.STATUS加入GROUP BY子句，代码如下：

SELECT
  TIT.PART_ID,
  TIT.TRACE_ID,
  IT.WAREHOUSE_ID,
  IT.LOCATION_ID,
  L.STATUS,
  P.PRIMARY_WHS_ID,
  P.PRIMARY_LOC_ID,
  P.BACKFLUSH_WHS_ID,
  P.BACKFLUSH_LOC_ID,
  P.AUTO_BACKFLUSH,
  SUM(TIT.QTY) AS QTY
FROM
TRACE_INV_TRANS TIT --TIT = TRACE INV TRANS TABLE
INNER JOIN INVENTORY_TRANS IT ON TIT.PART_ID = IT.PART_ID AND TIT.TRANSACTION_ID = IT.TRANSACTION_ID
LEFT JOIN PART_SITE P ON P.PART_ID = TIT.PART_ID
INNER JOIN PART_LOCATION L ON L.PART_ID = TIT.PART_ID 
WHERE
  IT.LOCATION_ID = 'DISPATCH'
  AND TIT.QTY IS NOT NULL
  AND L.STATUS = 'A'
GROUP BY
  TIT.TRACE_ID,
  TIT.PART_ID,
  IT.WAREHOUSE_ID,
  IT.LOCATION_ID,
  P.PRIMARY_WHS_ID,
  P.PRIMARY_LOC_ID,
  P.BACKFLUSH_WHS_ID,
  P.BACKFLUSH_LOC_ID,
  P.AUTO_BACKFLUSH,
  L.STATUS
HAVING
  SUM(TIT.QTY) > 0

执行后统计的QTY值约为正确值的9倍，存在明显异常。

问题根因 #

关联PART_LOCATION表时仅使用PART_ID作为关联条件，关联粒度不匹配：单个PART_ID在PART_LOCATION表中对应多条记录（该表数据粒度为物料+仓库+库位，平均每个物料存在9条STATUS='A'的库位记录），关联后产生笛卡尔积，原始1条记录被复制为9条，最终SUM聚合的QTY值被放大9倍。

修正方案 #

根据业务场景二选一即可：

• 方案1：补全关联条件，匹配对应维度，避免笛卡尔积。如果STATUS是物料在对应仓库/库位下的状态，需要把仓库、库位字段加入关联条件，示例：

INNER JOIN PART_LOCATION L 
  ON L.PART_ID = TIT.PART_ID 
  AND L.WAREHOUSE_ID = IT.WAREHOUSE_ID
  AND L.LOCATION_ID = IT.LOCATION_ID

• 方案2：如果仅需获取物料层面的有效状态，先对PART_LOCATION表做去重处理，保证每个PART_ID仅返回1条符合STATUS='A'的记录后再关联，示例：

INNER JOIN (
  SELECT PART_ID, STATUS
  FROM PART_LOCATION
  WHERE STATUS = 'A'
  GROUP BY PART_ID, STATUS
) L ON L.PART_ID = TIT.PART_ID

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