SQL内连接关联CERS列比较Tank ID时返回行数与预期不符的问题
问题根因
原查询返回远超预期的行数,核心原因是同CERS维度下的不等值连接触发了笛卡尔积交叉配对。
当同一个EC CERS在两个临时表中存在多条储罐记录时,a.[EC Tank ID] <> b.[CIW Tank ID]的条件会把同CERS下每一对ID不相等的组合全部返回,完全不是预期的“只找两边不匹配的差集对应关系”。
拿提供的示例数据计算,原查询会返回7条冗余配对:
| EC CERS | EC Tank ID | CIW Tank ID |
|---|---|---|
| 100338 | 1 | 001 |
| 100338 | 1 | 2 |
| 100338 | 1 | 3 |
| 100338 | 2 | 001 |
| 100338 | 2 | 3 |
| 100338 | 3 | 001 |
| 100338 | 3 | 2 |
这也是实际查询返回31行的根本原因。
简易实现方案
核心逻辑是先按CERS维度筛选两边的差集(即同CERS下,本侧存在、另一侧不存在的储罐ID),再按CERS配对,从根源上避免交叉冗余。
实现SQL如下:
WITH ec_side_diff AS ( -- 筛选EC侧:同CERS下,没有匹配到任何CIW Tank ID的记录 SELECT [EC CERS], [EC Tank ID] FROM #tempectank a WHERE NOT EXISTS ( SELECT 1 FROM #tempciwtank b WHERE b.[CIW CERS] = a.[EC CERS] AND b.[CIW Tank ID] = a.[EC Tank ID] ) ), ciw_side_diff AS ( -- 筛选CIW侧:同CERS下,没有匹配到任何EC Tank ID的记录 SELECT [CIW CERS], [CIW Tank ID] FROM #tempciwtank b WHERE NOT EXISTS ( SELECT 1 FROM #tempectank a WHERE a.[EC CERS] = b.[CIW CERS] AND a.[EC Tank ID] = b.[CIW Tank ID] ) ) -- 按CERS配对两边的差集,输出结果 SELECT e.[EC CERS], e.[EC Tank ID], c.[CIW Tank ID] FROM ec_side_diff e INNER JOIN ciw_side_diff c ON e.[EC CERS] = c.[CIW CERS]
用提供的示例数据执行上述SQL,会精准返回1行结果:
| EC CERS | EC Tank ID | CIW Tank ID |
|---|---|---|
| 100338 | 1 | 001 |
补充说明
- 这个方案不需要额外加
DISTINCT去重,因为筛出的差集本身就是唯一值,不会产生冗余行 - 如果遇到示例中
EC Tank ID = '1'和CIW Tank ID = '001'这类因为前导零、格式差异导致的假性不匹配,可以在匹配条件中加入数值转换,比如CAST(a.[EC Tank ID] AS INT) = CAST(b.[CIW Tank ID] AS INT),消除格式差异的影响 - 如果同个CERS下存在多组不匹配的差集,查询会自然返回对应数量的行,不会触发笛卡尔积交叉配对
内容的提问来源于stack exchange,提问作者TryingtoCode
相关产品推荐
相关产品推荐

