SQL 查找具有公共成员的集合(关系除法) [英] SQL find sets with common members (relational division)
本文介绍了SQL 查找具有公共成员的集合(关系除法)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一组单独的类"和组",每组都分配了一个或多个标签.我想为每个组找到包含每个组相同(或更多)标签的类的子集.
I have separate sets of "classes" and "groups", each of which has been assigned one or more tags. I would like to find, for each group, the subset of classes that contains the same (or more) tags for each group.
一些示例数据:
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
我理解这是一个关系除法类型的问题,涉及have
和count
.这些帖子描述了我想要做什么,但我不知道如何将示例应用于上述特定情况:
I understand this is a relational division type problem, involving having
and count
. These posts describe what I want to do, but I can't figure out how to apply the examples to the particular case above:
- http:///weblogs.sqlteam.com/peterl/archive/2010/07/02/Proper-Relational-Division-With-Sets.aspx
- 仅当所有记录都匹配时才加入 SQL 连接一个>
- 如何构建查询以仅提供与 T-SQL 中 ID 的 CSV 列表中的所有值匹配的行
- SQL:选择包含完全给定成员的集合
- 一个sql查询在定义的集合中查找具有共同标签的用户
推荐答案
我认为这也应该有效
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
这篇关于SQL 查找具有公共成员的集合(关系除法)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文