如何查询未完整使用规则组的Feed缺失的规则ID

需要编写SQL查询，找出至少使用了某规则组中一条规则，但未使用该组全部规则的Feed，同时返回对应的规则组ID和Feed缺失的规则ID。

表结构 #

TableA（规则与规则组映射） #

TableB（Feed使用的规则） #

期望查询结果 #

仅返回未完整使用规则组的Feed、对应规则组及缺失的规则ID：

结果说明 #

• Feed A已使用规则组573的全部规则，无结果；
• Feed B使用了规则组882的3条规则，缺失2条，返回这2条；
• Feed C未使用任何规则组的规则，无结果；
• Feed D使用了规则组450的1条规则，缺失3条，返回这3条。

尝试的错误SQL及问题 #

尝试的SQL存在语法错误（如Every derived table must have its own alias），且逻辑顺序错误：

SELECT B.feed, A.rulegroup, A.rule_id
FROM TableA AS A
JOIN TableB AS B ON A.rule_id = B.rule_id
GROUP BY B.feed, A.rulegroup
HAVING COUNT(B.rule_id) < (SELECT COUNT(*) FROM TableA WHERE rulegroup = A.rulegroup) AS total
LEFT JOIN TableA AS C ON A.rulegroup = C.rulegroup AND A.rule_id = C.rule_id

调整顺序后的语句仍未解决逻辑问题：

FROM TableA AS A
JOIN TableB AS B ON A.rule_id = B.rule_id
LEFT JOIN TableA AS C ON A.rulegroup = C.rulegroup AND A.rule_id = C.rule_id
GROUP BY B.feed, A.rulegroup
HAVING COUNT(B.rule_id) < (SELECT COUNT(*) FROM TableA WHERE rulegroup = A.rulegroup)

实际场景中，TableA和TableB均由约8次关联的查询生成，需尽量避免关联子查询以保证性能。

解决方案 #

方法1：使用CTE分步处理（清晰易读） #

WITH rule_group_total AS (
    -- 预计算每个规则组的总规则数
    SELECT rulegroup, COUNT(*) AS total_count
    FROM TableA
    GROUP BY rulegroup
),
feed_used_rules AS (
    -- 计算每个Feed在各规则组中已使用的规则数
    SELECT 
        b.feed,
        a.rulegroup,
        COUNT(DISTINCT b.rule_id) AS used_count
    FROM TableB b
    JOIN TableA a ON b.rule_id = a.rule_id
    GROUP BY b.feed, a.rulegroup
),
incomplete_groups AS (
    -- 筛选出未完整使用规则组的Feed
    SELECT 
        fur.feed,
        fur.rulegroup,
        rgt.total_count
    FROM feed_used_rules fur
    JOIN rule_group_total rgt ON fur.rulegroup = rgt.rulegroup
    WHERE fur.used_count < rgt.total_count
)
-- 找出Feed在对应规则组中缺失的规则
SELECT 
    ig.feed,
    ig.rulegroup,
    a.rule_id
FROM incomplete_groups ig
JOIN TableA a ON ig.rulegroup = a.rulegroup
LEFT JOIN TableB b ON ig.feed = b.feed AND a.rule_id = b.rule_id
WHERE b.rule_id IS NULL
ORDER BY ig.feed, ig.rulegroup, a.rule_id;

逻辑说明 #

1. 预计算规则组总规则数：先统计每个规则组包含的规则总数，避免重复计算；
2. 筛选未完整使用的Feed+规则组：关联TableB和TableA，计算每个Feed在各规则组的已用规则数，筛选出已用数小于总数的组合；
3. 找出缺失规则：将筛选后的Feed+规则组与该组的所有规则关联，左连接TableB排除已用规则，剩余即为缺失规则。

这种分步处理的方式避免了嵌套子查询的性能问题，也更适配TableA、TableB是多关联查询生成的场景（只需将CTE中的TableA、TableB替换为对应的关联查询即可）。

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