SQL同表数据比对：识别productfops表缺失的实体-服务-fop组合

我有一张名为productfops的表，包含entity、services、fop字段，现有数据如下：

需要识别两类不存在的组合：

• 实体+已有服务+未关联fop的组合：比如Gpl、YouTube、debit（Gpl实体下存在debit这个fop，但YouTube服务未关联它）
• 实体+新服务+已有fop的组合：比如Gpl、gsuit、credit（gsuit是该实体未使用的服务，但credit是Gpl已有的fop）

尝试过两个无效SQL脚本，请求正确解决方案。

无效脚本分析 #

1. 第一个脚本：

SELECT entity, service, fop
FROM tableA
WHERE (entity, service) IN (
    SELECT entity, service
    FROM tableA
    GROUP BY entity, service
    HAVING COUNT(*) > 1
);

问题：原表中每个entity+services组合都是唯一的，COUNT(*) > 1的条件永远不满足，完全无法匹配目标组合。

2. 第二个脚本：

CREATE OR REPLACE TABLE b AS
(
    -- Combinations of two columns (entity and service)
    SELECT a.entity, a.service, NULL AS fop
    FROM a
    WHERE CONCAT(a.entity, ' - ', a.service) NOT IN (
        SELECT CONCAT(b.entity, ' - ', b.service)
        FROM b
    )
    UNION ALL
    -- Combinations of three columns (entity, service, and fop)
    SELECT a.entity, a.service, a.fop
    FROM a
    WHERE CONCAT(a.entity, ' - ', a.service, ' - ', a.fop) NOT IN (
        SELECT CONCAT(b.entity, ' - ', b.service, ' - ', b.fop)
        FROM b
    )
)

问题：脚本引用了正在创建的表b作为子查询数据源，存在循环依赖；且用CONCAT拼接字符串的方式容易因特殊字符出现匹配错误，同时完全没有针对两类目标组合的逻辑设计。

我们可以分两部分生成目标组合，再通过UNION ALL合并结果：

1. 生成「实体+已有服务+未关联fop」的组合 #

思路：先获取每个实体下所有唯一的服务和fop，将两者做笛卡尔积生成所有可能的组合，再排除表中已存在的entity+services+fop组合，剩下的就是这类缺失组合。

-- 第一类：实体+已有服务+未关联fop
SELECT 
    s.entity,
    s.services,
    f.fop
FROM 
    (SELECT DISTINCT entity, services FROM productfops) s
CROSS JOIN 
    (SELECT DISTINCT entity, fop FROM productfops) f
WHERE 
    s.entity = f.entity
    AND NOT EXISTS (
        SELECT 1 
        FROM productfops p
        WHERE p.entity = s.entity 
          AND p.services = s.services 
          AND p.fop = f.fop
    )

2. 生成「实体+新服务+已有fop」的组合 #

这里的「新服务」指该实体未使用的服务（比如示例中的gsuit），我们可以通过定义临时数据集来包含这些新服务：

-- 第二类：实体+新服务+已有fop
SELECT 
    p.entity,
    ns.new_service AS services,
    p.fop
FROM 
    -- 定义新服务列表，多个服务可通过UNION ALL扩展
    (SELECT 'gsuit' AS new_service) ns
CROSS JOIN 
    (SELECT DISTINCT entity, fop FROM productfops) p
WHERE 
    -- 确保该实体尚未使用这个新服务
    NOT EXISTS (
        SELECT 1 
        FROM productfops pf
        WHERE pf.entity = p.entity 
          AND pf.services = ns.new_service
    )

合并两类结果 #

将上述两个查询用UNION ALL合并，即可得到目标输出：

-- 最终完整查询
SELECT 
    s.entity,
    s.services,
    f.fop
FROM 
    (SELECT DISTINCT entity, services FROM productfops) s
CROSS JOIN 
    (SELECT DISTINCT entity, fop FROM productfops) f
WHERE 
    s.entity = f.entity
    AND NOT EXISTS (
        SELECT 1 
        FROM productfops p
        WHERE p.entity = s.entity 
          AND p.services = s.services 
          AND p.fop = f.fop
    )

UNION ALL

SELECT 
    p.entity,
    ns.new_service AS services,
    p.fop
FROM 
    (SELECT 'gsuit' AS new_service) ns
CROSS JOIN 
    (SELECT DISTINCT entity, fop FROM productfops) p
WHERE 
    NOT EXISTS (
        SELECT 1 
        FROM productfops pf
        WHERE pf.entity = p.entity 
          AND pf.services = ns.new_service
    )
ORDER BY entity, services, fop;

补充说明 #

• 若有多个新服务，可扩展临时数据集，例如：(SELECT 'gsuit' AS new_service UNION ALL SELECT 'drive' AS new_service)
• 使用NOT EXISTS替代字符串拼接，避免字符干扰，逻辑更严谨高效

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