T-SQL-如何编写查询以获取与多对多联接中的所有记录匹配的记录 [英] T-SQL - How to write query to get records that match ALL records in a many to many join

问题描述

(我认为这个问题的标题不正确-但我不知道如何描述它)

(I don't think I have titled this question correctly - but I don't know how to describe it)

这就是我想要做的:

假设我有一个包含PersonID字段的Person表.假设一个人可以属于多个组.因此，存在一个具有GroupID字段的Group表和一个GroupMembership表，该表是两个表之间的多对多联接，并且GroupMembership表具有PersonID字段和GroupID字段.到目前为止，这是一个简单的多对多连接.

Let's say I have a Person table that has a PersonID field. And let's say that a Person can belong to many Groups. So there is a Group table with a GroupID field and a GroupMembership table that is a many-to-many join between the two tables and the GroupMembership table has a PersonID field and a GroupID field. So far, it is a simple many to many join.

给出一个GroupID列表，我希望能够编写一个查询，该查询返回所有这些组中的所有人员(而不是那些组中的任何一个).并且查询应该能够处理任意数量的GroupID.我想避免使用动态SQL.

Given a list of GroupIDs I would like to be able to write a query that returns all of the people that are in ALL of those groups (not any one of those groups). And the query should be able to handle any number of GroupIDs. I would like to avoid dynamic SQL.

我是否缺少一些简单的方法来做到这一点? 谢谢， 科里

Is there some simple way of doing this that I am missing? Thanks, Corey

推荐答案

select person_id, count(*) from groupmembership
where group_id in ([your list of group ids])
group by person_id
having count(*) = [size of your list of group ids]

谢谢dotjoe！

这篇关于T-SQL-如何编写查询以获取与多对多联接中的所有记录匹配的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！