我有单独的“类”和“组”集,每个组都分配了一个或多个标签。我想为每个组找到包含每个组相同(或更多)标签的类的子集。
一些样本数据:
declare @Groups table ( GroupID int, TagID int ) insert @Groups values (1,1),(1,2),(1,3), (2,1),(2,2), (3,1),(3,2),(3,3),(3,4) declare @Classes table ( ClassID int, TagID int ) insert @Classes values (1,1),(1,2), (2,1),(2,2), (3,1),(3,2),(3,3) select * from @Groups select * from @Classes
并输出:
GroupID TagID 1 1 1 2 1 3 2 1 2 2 3 1 3 2 3 3 3 4 ClassID TagID 1 1 1 2 2 1 2 2 3 1 3 2 3 3
结果集示例如下所示:
declare @Results table ( GroupID int, ClassID int ) insert @Results values (1,3),(2,1),(2,2),(2,3),(3,null) select * from @Results
结果输出:
GroupID ClassID 1 3 2 1 2 2 2 3 3 NULL
我了解这是一个涉及having和的关系划分类型问题count。这些帖子描述了我想做的事情,但我不知道
having
count
我认为这也应该起作用
select distinct g.GroupID, c.ClassID from @Groups g left join @Classes c on g.TagID = c.TagID where not exists ( select * from @Groups g2 where g2.GroupID = g.GroupID and g2.TagID not in ( select TagID from @Classes c2 where c2.ClassID = c.ClassID ) ) or c.ClassID is null