根据在另一列的标识符内的所有行中匹配列内容来选择行?

Question

在Windows 10 Enterprise version 21H1上运行SQL Server 14.0.2037.2

我试图返回ID只有当另一个列的条目在该ID内的所有行的匹配类似的列在第二个表中的条目，再次在特定的ID内。例如,

create table #F(patentid VARCHAR(8), subclass VARCHAR(3));
insert into #F values ('l','x');
insert into #F values ('l','y');
insert into #F values ('l','z');
insert into #F values ('m','x');
insert into #F values ('n','z');

create table #P(patentid VARCHAR(8), subclass VARCHAR(3));

insert into #P values ('b','x');
insert into #P values ('c','w');
insert into #P values ('c','x');
insert into #P values ('c','y');
insert into #P values ('c','z');
insert into #P values ('d','x');
insert into #P values ('d','y');
insert into #P values ('d','z');
insert into #P values ('e','x');
insert into #P values ('e','y');
insert into #P values ('e','z');

我正试图编写一个查询，它将返回，对于每个patentid在#F，所有patentid在# p，其中子类条目完全匹配。这对于"l"在#F中匹配"one_answers";e"在#P中[匹配的子类是'x'， 'y'和'z']还有"m"#F中与"b"在#P中[匹配的子类是"x"]

输出应该是:

l, d
l, e
m, b

代码需要高效，因为#F和# p都包含数百万行。我已经尝试过联合，但这适用于整个行集，不允许由子集(即在专利内)匹配

任何帮助都非常感谢。

Answer 1

这是一个关系除法无余数的例子，有多个除法.

换句话说，你想用(在关系意义上)#F除以#P，并且只取没有余数的结果。

有很多解决方案，这里有一个

• 用patentid划分两个表并计算窗口计数
• 通过subclass左连接一个表到另一个表，过滤精确匹配计数
• 按id对分组
• 过滤掉任何不匹配p.subclass的f.subclass，我们对HAVING COUNT(*) = COUNT(p.subclass)这样做，因为COUNT(p.subclass)只计算非空。

SELECT
f.patentid, p.patentid
FROM (
SELECT *,
cnt = COUNT(*) OVER (PARTITION BY f.patentid)
FROM #F f
) f
LEFT JOIN (
SELECT *,
cnt = COUNT(*) OVER (PARTITION BY p.patentid)
FROM #P p
) p ON p.cnt = f.cnt AND p.subclass = f.subclass
GROUP BY
f.patentid, p.patentid
HAVING COUNT(*) = COUNT(p.subclass);

,db&lt的在小提琴

Answer 2

这对你来说足够有效吗?

select f.patentId, p.patentid 
from (
select patentId, STRING_AGG(subclass,'') WITHIN GROUP (ORDER BY subclass ASC) as class 
from #F 
group by patentId
) f
inner join (
select patentId, STRING_AGG(subclass,'') WITHIN GROUP (ORDER BY subclass ASC) as class 
from #P 
group by patentId
) p on f.class = p.class