SQL JOIN对比车辆表group_size结果不符，如何修正？

我有两张表，希望对比它们各自的group_size：

表结构与测试数据 #

create table vehicle1
(
V_id int,
make nvarchar(20),
model nvarchar(20),
group_size1 nvarchar(20),
tech varchar(20)
);

Insert into vehicle1 values
( 1, 'audi', 'q5' ,'34','SLI'),
( 1, 'audi', 'q5' ,'35','SLI'),
( 2, 'mini', 'cooper' ,'35','SLI'),
( 3, 'mazda', 'a3' ,'21','AGM'),
( 4, 'audi', 'q5' ,'H4','SLI');

create table vehicle2
(
V_id int,
make nvarchar(20),
model nvarchar(20),
group_size2 nvarchar(20),
tech varchar(20)
);

Insert into vehicle2 values
( 1, 'audi', 'q5' ,'34','SLI'),
( 1, 'audi', 'q5' ,'35','SLI'),
( 2, 'mini', 'cooper' ,'35','SLI'),
( 3, 'mazda', 'a3' ,'30','AGM'),
( 3, 'mazda', 'a3' ,'21','AGM'),
( 4, 'audi', 'q5' ,'H5','SLI');

期望输出 #

V_id    make    model   group_size1 group_size2 tech
( 3, 'mazda', 'a3' ,'21', '30','AGM'),
( 4, 'audi', 'q5' , 'H4', 'H5','SLI');

尝试的SQL语句 #

SELECT v1.V_id, v1.make, v1.model, v1.group_size1, v2.group_size2, v1.tech
FROM vehicle1 v1
JOIN vehicle2 v2 ON v1.V_id = v2.V_id and v1.make = v2.make and v1.model = v2.model and v1.group_size1 <> v2.group_size2;

实际得到的输出 #

( 1, 'audi', 'q5' ,'34', '35','SLI'),
( 1, 'audi', 'q5' ,'35', '34', 'SLI'),
( 3, 'mazda', 'a3' ,'21', '30','AGM'),
( 4, 'audi', 'q5' , 'H4', 'H5','SLI');

我不理解为何V_id=1、group_size1=35的记录仍被纳入对比，请问需要考虑哪些因素才能得到预期输出？

原因分析 #

V_id=1在两张表里都包含34和35两个group_size值，你的JOIN条件仅判断单条记录的group_size是否不等，所以会产生交叉匹配：34和35匹配、35和34匹配，这就导致两条冗余记录被筛选出来。但实际上V_id=1的两张表group_size集合完全一致，不属于需要展示的差异项。

需要考虑的关键因素 #

• 分组集合的一致性：不能仅对比单条记录的group_size，要先按V_id+make+model+tech分组，判断两个表中同一分组的group_size整体集合是否完全相同。只有集合不同的分组才是真正的差异项。
• 避免交叉匹配冗余：同一分组内的多条记录直接JOIN会产生笛卡尔积，即使集合一致，也会出现大量单条记录不等的匹配结果。

解决方法 #

先识别出group_size集合不一致的分组，再从这些分组中筛选差异记录，排除集合一致的分组：

WITH v1_groups AS (
    SELECT V_id, make, model, tech, STRING_AGG(group_size1, ',') WITHIN GROUP (ORDER BY group_size1) AS size_set
    FROM vehicle1
    GROUP BY V_id, make, model, tech
),
v2_groups AS (
    SELECT V_id, make, model, tech, STRING_AGG(group_size2, ',') WITHIN GROUP (ORDER BY group_size2) AS size_set
    FROM vehicle2
    GROUP BY V_id, make, model, tech
),
matching_groups AS (
    SELECT v1.V_id, v1.make, v1.model, v1.tech
    FROM v1_groups v1
    JOIN v2_groups v2 ON v1.V_id = v2.V_id 
        AND v1.make = v2.make 
        AND v1.model = v2.model 
        AND v1.tech = v2.tech 
        AND v1.size_set = v2.size_set
)
SELECT v1.V_id, v1.make, v1.model, v1.group_size1, v2.group_size2, v1.tech
FROM vehicle1 v1
JOIN vehicle2 v2 ON v1.V_id = v2.V_id 
    AND v1.make = v2.make 
    AND v1.model = v2.model 
    AND v1.group_size1 <> v2.group_size2
WHERE NOT EXISTS (
    SELECT 1 FROM matching_groups mg
    WHERE mg.V_id = v1.V_id 
        AND mg.make = v1.make 
        AND mg.model = v1.model 
        AND mg.tech = v1.tech
);

这段SQL先通过分组聚合得到每个分组的group_size集合，找出集合完全匹配的分组并排除，最终只保留集合不一致的分组中的差异记录，符合你的预期输出。

内容的提问来源于stack exchange，提问作者Abhiram Reddy Kotu