如何在无动态SQL下实现两表GRP完全双向匹配的查询？

需求说明 #

需要找出满足以下条件的WorkplaceID和对应的UnitID：

• 该Workplace的所有GRP条目都存在于对应Unit的GRP集合中
• 该Unit的所有GRP条目也都存在于对应Workplace的GRP集合中
  简言之，两者的GRP集合需要完全相等，不能有任何缺失或多余的GRP。

最小可复现示例 #

1. 创建测试表及数据

-- 创建WorkplaceCapabilities表（存储工位可处理的GRP）
CREATE TABLE WorkplaceCapabilities (
    WorkplaceID INT,
    GRP VARCHAR(50),
    PRIMARY KEY (WorkplaceID, GRP) -- 确保同一工位不会重复记录同一GRP
);

INSERT INTO WorkplaceCapabilities (WorkplaceID, GRP)
VALUES
(1, 'GRP_A'),
(1, 'GRP_B'),
(2, 'GRP_A'),
(3, 'GRP_B'),
(3, 'GRP_C'),
(4, 'GRP_A'),
(4, 'GRP_B'),
(4, 'GRP_C');

-- 创建TransportUnitContents表（存储运输单元包含的GRP）
CREATE TABLE TransportUnitContents (
    UnitID INT,
    GRP VARCHAR(50),
    PRIMARY KEY (UnitID, GRP) -- 确保同一单元不会重复记录同一GRP
);

INSERT INTO TransportUnitContents (UnitID, GRP)
VALUES
(101, 'GRP_A'),
(101, 'GRP_B'),
(102, 'GRP_A'),
(103, 'GRP_B'),
(103, 'GRP_D'),
(104, 'GRP_A'),
(104, 'GRP_B'),
(104, 'GRP_C');

2. 实现完全匹配的查询语句

SELECT 
    wc.WorkplaceID,
    tu.UnitID
FROM 
    (SELECT DISTINCT WorkplaceID FROM WorkplaceCapabilities) wc
CROSS JOIN 
    (SELECT DISTINCT UnitID FROM TransportUnitContents) tu
WHERE 
    -- 检查工位的GRP全部存在于运输单元中
    NOT EXISTS (
        SELECT GRP FROM WorkplaceCapabilities WHERE WorkplaceID = wc.WorkplaceID
        EXCEPT
        SELECT GRP FROM TransportUnitContents WHERE UnitID = tu.UnitID
    )
    -- 同时检查运输单元的GRP全部存在于工位中
    AND NOT EXISTS (
        SELECT GRP FROM TransportUnitContents WHERE UnitID = tu.UnitID
        EXCEPT
        SELECT GRP FROM WorkplaceCapabilities WHERE WorkplaceID = wc.WorkplaceID
    );

逻辑说明 #

• 先通过DISTINCT获取所有独立的工位和运输单元ID，做笛卡尔积遍历所有可能的组合
• 使用EXCEPT运算符比较两个GRP集合：
  • 第一个NOT EXISTS确保工位的GRP没有超出运输单元的范围
  • 第二个NOT EXISTS确保运输单元的GRP没有超出工位的范围
• 只有当两个EXCEPT的结果集都为空时，才说明两个GRP集合完全匹配，返回对应ID

测试结果 #

执行上述查询后，会得到以下匹配结果：

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