SQL按分组计算两同结构表amount列差值的实现问题

你当前SQL出现交叉错配结果，是两个核心逻辑错误导致的：

• 左连接的关联条件仅匹配了idProduct，没有将classification作为同粒度匹配的判断条件，直接让同一产品下的不同分类记录两两交叉匹配：比如table1中分类A（amount=1）会匹配到table2中分类AA（amount=16）、table1中分类AA（amount=18）会匹配到table2中分类A（amount=1），最终生成了4行中间结果，对应你看到的差值-15、17就是这么来的
• 分组时将单条记录的p.amount、s.amount作为分组字段，没有严格按照预期的idProduct + classification维度聚合，最终把交叉匹配的明细行全部输出成了独立结果

写法1：修正关联条件+标准分组聚合 #

这是针对你当前数据场景最简洁的实现，核心是补充分类维度的关联判断，再按业务维度聚合计算差值：

SELECT 
  p.[idProduct],
  p.classification,
  ISNULL(SUM(p.[amount]), 0) - ISNULL(SUM(s.[amount]), 0) AS inStock
FROM table1 AS p
LEFT JOIN table2 AS s 
  ON p.[idProduct] = s.[idProduct] 
  AND p.classification = s.classification
GROUP BY p.[idProduct], p.classification

执行后会稳定输出你需要的2行结果：

• idProduct=1、classification=A：差值1-1=0
• idProduct=1、classification=AA：差值18-16=2
  说明：使用SUM聚合是为了适配同一个产品+分类下存在多条明细记录的场景，避免单条记录的匹配错漏

写法2：全维度适配版（兼容两表独有维度） #

如果后续存在某张表独有某类产品+分类记录的场景，写法1的左连接会遗漏这些维度，这时可以用先合并所有维度再关联计算的方案，覆盖所有业务场景：

WITH all_biz_dimension AS (
  -- 去重合并两张表的所有产品+分类维度
  SELECT DISTINCT idProduct, classification FROM table1
  UNION 
  SELECT DISTINCT idProduct, classification FROM table2
)
SELECT 
  d.idProduct,
  d.classification,
  ISNULL(p.amount, 0) - ISNULL(s.amount, 0) AS inStock
FROM all_biz_dimension d
LEFT JOIN table1 p 
  ON d.idProduct = p.idProduct 
  AND d.classification = p.classification
LEFT JOIN table2 s 
  ON d.idProduct = s.idProduct 
  AND d.classification = s.classification

• 做跨表差值计算时，关联条件必须完全覆盖业务匹配的所有粒度维度，仅靠单主键关联极易触发笛卡尔交叉匹配
• 分组逻辑要和输出维度严格对齐，不能把单条明细的计算值（比如单条记录的amount）放进GROUP BY子句，否则会把需要聚合的明细拆成独立输出行

内容的提问来源于stack exchange，提问作者Maykon Luiz Matos Araujo