SQL Server 两表求和相减计算最终库存并将超卖负库存置零

问题原因 #

当前查询结果异常的核心原因是直接关联两张表后再聚合，会产生笛卡尔积：筛选条件下库存表（@COLORSIZEQTYS）符合要求的记录有3条，采购表（@STORECOLORSIZEEST）符合要求的记录有4条，内连接后会生成3*4=12条重复记录，求和时数值被重复累加，导致结果偏离预期。

修正方案 #

正确的做法是先分别对两张表做聚合计算，再关联做差值运算，避免数据重复计算：

1. 不含负库存置零的查询 #

SELECT 
  inv.QTY4 - pcs.RSV4 AS TOT4,
  inv.QTY5 - pcs.RSV5 AS TOT5,
  inv.QTY6 - pcs.RSV6 AS TOT6,
  inv.QTY7 - pcs.RSV7 AS TOT7
FROM 
  (
    -- 先计算库存总数量
    SELECT 
      SUM(SIZE4) AS QTY4,
      SUM(SIZE5) AS QTY5,
      SUM(SIZE6) AS QTY6,
      SUM(SIZE7) AS QTY7
    FROM @COLORSIZEQTYS
    WHERE ITEID = 5594 AND COLORCODE = 'Grey' AND QTYMODE = 1
  ) inv
CROSS JOIN 
  (
    -- 再计算采购总数量
    SELECT 
      SUM(SIZE4) AS RSV4,
      SUM(SIZE5) AS RSV5,
      SUM(SIZE6) AS RSV6,
      SUM(SIZE7) AS RSV7
    FROM @STORECOLORSIZEEST
    WHERE ITEID = 5594 AND COLORCODE = 'Grey'
  ) pcs

运行结果和未置零的预期完全一致：

2. 包含负库存置零的查询 #

只需要在差值外套一层判断，小于0则返回0即可：

SELECT 
  CASE WHEN inv.QTY4 - pcs.RSV4 < 0 THEN 0 ELSE inv.QTY4 - pcs.RSV4 END AS TOT4,
  CASE WHEN inv.QTY5 - pcs.RSV5 < 0 THEN 0 ELSE inv.QTY5 - pcs.RSV5 END AS TOT5,
  CASE WHEN inv.QTY6 - pcs.RSV6 < 0 THEN 0 ELSE inv.QTY6 - pcs.RSV6 END AS TOT6,
  CASE WHEN inv.QTY7 - pcs.RSV7 < 0 THEN 0 ELSE inv.QTY7 - pcs.RSV7 END AS TOT7
FROM 
  (
    SELECT 
      SUM(SIZE4) AS QTY4,
      SUM(SIZE5) AS QTY5,
      SUM(SIZE6) AS QTY6,
      SUM(SIZE7) AS QTY7
    FROM @COLORSIZEQTYS
    WHERE ITEID = 5594 AND COLORCODE = 'Grey' AND QTYMODE = 1
  ) inv
CROSS JOIN 
  (
    SELECT 
      SUM(SIZE4) AS RSV4,
      SUM(SIZE5) AS RSV5,
      SUM(SIZE6) AS RSV6,
      SUM(SIZE7) AS RSV7
    FROM @STORECOLORSIZEEST
    WHERE ITEID = 5594 AND COLORCODE = 'Grey'
  ) pcs

运行结果和置零后的预期完全一致：

内容的提问来源于stack exchange，提问作者Faye D.