如何用SQL确保每个Assortment分组包含相同的UPC？

问题背景 #

现有一张表，Assortment是包含多个产品的分组，UPC为产品ID，需验证每个Assortment包含的UPC完全一致。以下是SQL Server 2014的测试数据：

正确示例：所有分组UPC完全一致 #

DECLARE @input TABLE (Assortment VARCHAR(100) NOT NULL, UPC VARCHAR(20) NOT NULL);

INSERT INTO @input (Assortment,UPC)
VALUES ('A','1'),('A','2'),('B','1'),('B','2');

错误示例：分组UPC存在差异 #

DECLARE @input TABLE (Assortment VARCHAR(100) NOT NULL, UPC VARCHAR(20) NOT NULL);

INSERT INTO @input (Assortment,UPC)
VALUES ('A','1'),('A','2'),('B','1'),('B','3');

失效场景：数量一致但UPC完全不同 #

此前通过统计分组产品数、UPC所属分组数的方法，在以下场景会失效：

DECLARE @input TABLE (Assortment VARCHAR(100) NOT NULL, UPC VARCHAR(20) NOT NULL);

INSERT INTO @input (Assortment,UPC)
VALUES ('A','1'),('A','2'),('B','3'),('B','4');

可行解决方案 #

方法1：生成有序UPC集合的哈希值对比 #

核心思路：为每个Assortment生成排序后UPC拼接字符串的哈希值，若所有分组的哈希值相同，则说明UPC集合完全一致。

SQL代码：

DECLARE @input TABLE (Assortment VARCHAR(100) NOT NULL, UPC VARCHAR(20) NOT NULL);
-- 替换为你的测试数据
INSERT INTO @input (Assortment,UPC)
VALUES ('A','1'),('A','2'),('B','1'),('B','2');

-- 生成每个分组的UPC集合哈希
WITH GroupUPCHashes AS (
    SELECT 
        Assortment,
        HASHBYTES('SHA2_256', 
            (SELECT UPC + ',' 
             FROM @input i2 
             WHERE i2.Assortment = i1.Assortment 
             ORDER BY UPC 
             FOR XML PATH(''))
        ) AS UPCHash
    FROM @input i1
    GROUP BY Assortment
)
-- 检查是否所有哈希值一致
SELECT 
    CASE WHEN COUNT(DISTINCT UPCHash) = 1 
         THEN '所有Assortment的UPC完全一致' 
         ELSE '存在Assortment的UPC集合不一致' 
    END AS 验证结果;

方法2：直接找出不一致的分组对 #

如果需要定位具体哪些分组存在差异，可以用以下代码找出所有不匹配的组合：

DECLARE @input TABLE (Assortment VARCHAR(100) NOT NULL, UPC VARCHAR(20) NOT NULL);
INSERT INTO @input (Assortment,UPC)
VALUES ('A','1'),('A','2'),('B','1'),('B','3'),('C','1'),('C','2');

-- 找出所有分组间的UPC差异
SELECT 
    a.Assortment AS 分组1,
    b.Assortment AS 分组2,
    CASE 
        WHEN EXISTS (SELECT UPC FROM @input WHERE Assortment = a.Assortment EXCEPT SELECT UPC FROM @input WHERE Assortment = b.Assortment)
        THEN '分组1存在分组2没有的UPC'
        ELSE '分组2存在分组1没有的UPC'
    END AS 差异类型
FROM (SELECT DISTINCT Assortment FROM @input) a
CROSS JOIN (SELECT DISTINCT Assortment FROM @input) b
WHERE a.Assortment < b.Assortment -- 避免重复对比
AND EXISTS (
    SELECT UPC FROM @input WHERE Assortment = a.Assortment
    EXCEPT
    SELECT UPC FROM @input WHERE Assortment = b.Assortment
)
OR EXISTS (
    SELECT UPC FROM @input WHERE Assortment = b.Assortment
    EXCEPT
    SELECT UPC FROM @input WHERE Assortment = a.Assortment
);

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