SQL Server如何实现两表按patentid分组的subclass全匹配查询

适用环境 #

Windows 10 企业版21H1 + SQL Server 14.0.2037.2

实现思路 #

要实现两个表按patentid分组的subclass集合完全匹配，只需要满足两个核心校验条件：

• 同一个patentid下的subclass总条数完全相等
• 两个patentid之间互相匹配的subclass条数等于前者的总条数

性能前置优化（适配百万级数据必做） #

提前为两个临时表创建覆盖索引，避免全表扫描：

-- #F表索引，按patentid分组，包含subclass用于匹配
CREATE NONCLUSTERED INDEX IX_F_PatentSubclass ON #F(patentid, subclass);
-- #P表索引
CREATE NONCLUSTERED INDEX IX_P_PatentSubclass ON #P(patentid, subclass);

高效查询代码 #

WITH 
-- 预计算#F每个patentid的subclass数量
F_Count AS (
    SELECT patentid, COUNT(*) AS cnt
    FROM #F
    GROUP BY patentid
),
-- 预计算#P每个patentid的subclass数量
P_Count AS (
    SELECT patentid, COUNT(*) AS cnt
    FROM #P
    GROUP BY patentid
)
SELECT 
    f.patentid AS F_patentid,
    p.patentid AS P_patentid
FROM F_Count f
-- 先过滤数量相等的候选，大幅缩小匹配范围
INNER JOIN P_Count p ON f.cnt = p.cnt
-- 校验所有subclass完全匹配，没有多余或缺失
INNER JOIN #F f_detail ON f.patentid = f_detail.patentid
INNER JOIN #P p_detail ON p.patentid = p_detail.patentid AND f_detail.subclass = p_detail.subclass
GROUP BY f.patentid, p.patentid, f.cnt
-- 匹配到的条数等于总条数，说明集合完全一致
HAVING COUNT(*) = f.cnt
ORDER BY f.patentid, p.patentid

测试结果验证 #

针对你提供的测试数据，执行上述查询后输出结果如下：

F_patentid P_patentid
---------- ----------
l          d
l          e
m          b

和预期输出完全一致，#F中的patentid n因为#P中没有仅含z的patentid，所以不会返回。

性能说明 #

• 预计算数量的CTE仅需扫描两个表各一次，借助索引可以做到索引扫描而非全表扫描
• 先通过数量相等过滤掉绝大多数不匹配的组合，避免无效的subclass匹配计算
• 后续的join和分组聚合都可以借助预先创建的索引加速，百万级数据量下可以做到秒级返回

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