SQL关联box与container表按两列排序获取去重结果的实现问题

解决方案 #

你遇到数据翻倍的核心原因是：相同关联维度下，#box有2个不重复BoxId，#container有2个不重复ContainerId，直接关联后会产生笛卡尔积2*2=4条结果。你需要的是同维度下两个表的唯一ID按固定排序一一配对，实现方式如下：

-- 先分别对两个表按维度分组，获取去重后的ID列表并按ID排序生成稳定行号
WITH DistinctBox AS (
    SELECT DISTINCT OrderId, BoxId, width, height, weight
    FROM #box
),
BoxRanked AS (
    SELECT *, 
        ROW_NUMBER() OVER(PARTITION BY OrderId, width, height, weight ORDER BY BoxId ASC) AS rn
    FROM DistinctBox
),
DistinctContainer AS (
    SELECT DISTINCT OrderId, ContainerId, width, height, weight
    FROM #container
),
ContainerRanked AS (
    SELECT *, 
        ROW_NUMBER() OVER(PARTITION BY OrderId, width, height, weight ORDER BY ContainerId ASC) AS rn
    FROM DistinctContainer
)
SELECT b.OrderId, c.ContainerId, b.BoxId, b.width, b.height, b.weight
FROM BoxRanked b
INNER JOIN ContainerRanked c 
    ON b.OrderId = c.OrderId
    AND b.width = c.width
    AND b.height = c.height
    AND b.weight = c.weight
    AND b.rn = c.rn
ORDER BY c.ContainerId, b.BoxId

方案说明 #

• 首先通过DISTINCT分别清洗两个表的重复行，确保每个ID+维度组合只保留1条
• 生成行号时指定按主键ID本身排序，因为ID是唯一值，所以行号生成规则完全稳定，不存在匹配不稳定的问题
• 最终关联时加上行号匹配条件，同维度下第N个Box匹配第N个Container，即可得到你要的2条结果

如果你确实完全不想用ROW_NUMBER，也可以用聚合匹配的方式实现，适合两个表同维度下ID数量完全对等的场景：

WITH BoxAgg AS (
    SELECT OrderId, width, height, weight,
        STRING_AGG(BoxId, ',') WITHIN GROUP (ORDER BY BoxId) AS BoxIds
    FROM (SELECT DISTINCT OrderId, BoxId, width, height, weight FROM #box) t
    GROUP BY OrderId, width, height, weight
),
ContainerAgg AS (
    SELECT OrderId, width, height, weight,
        STRING_AGG(ContainerId, ',') WITHIN GROUP (ORDER BY ContainerId) AS ContainerIds
    FROM (SELECT DISTINCT OrderId, ContainerId, width, height, weight FROM #container) t
    GROUP BY OrderId, width, height, weight
)
SELECT b.OrderId, 
    CAST(value AS INT) AS BoxId,
    CAST(JSON_VALUE('["' + REPLACE(ContainerIds, ',', '","') + '"]', CONCAT('$[', [key], ']')) AS INT) AS ContainerId,
    b.width, b.height, b.weight
FROM BoxAgg b
INNER JOIN ContainerAgg c 
    ON b.OrderId = c.OrderId AND b.width = c.width AND b.height = c.height AND b.weight = c.weight
CROSS APPLY OPENJSON('["' + REPLACE(BoxIds, ',', '","') + '"]')
ORDER BY ContainerId, BoxId

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