MySQL查询类似相交的函数 [英] MySQL query for Intersect-like function

问题描述

我有一个Person表，Group表和Group_Person表，其中许多人员实体可以具有许多组实体. Group_Person充当中间表，并具有映射到Group表中的group_id和Person表中的person_id的元组(group_id，person_id).

I have a Person table, Group table, and Group_Person table, where many person entities can have many group entities. Group_Person acts as the intermediate table, and have the tuple (group_id, person_id) that maps to group_id from Group table, and person_id from Person table.

给定Person表中的0个或更多person_id(从Person表中查询)，我想从Group表中找到所有Groups，在Group_Person表中，每个person_id有一个元组(group_id，person_id).换句话说，每个person_ids都映射到一个公共的group_id.

Given 0 or more person_id from Person table (that is queried from Person table), I want to find all Groups from Group table where, in Group_Person table, there is a tuple (group_id, person_id) for each person_id. In other words, each person_ids is mapped to a common group_id.

由于MySQL没有INTERSECT函数，因此我正在考虑执行以下查询(此查询仅返回group_id):

Since MySQL has no INTERSECT function, I was thinking of doing the following query (this returns only the group_id):

SELECT t1.group_id 
FROM Group_Person AS t1 
JOIN Group_Person AS t2 ON t1.group_id = t2.group_id 
WHERE t1.person_id = xxx AND t2.person_id = yyy;

同样，这里可以有0个或更多的person_id.这是最好的解决方案吗?

Again, there can be 0 or more person_ids here. Is this the best solution?

推荐答案

我不认为多次加入group_person会为您效劳.您可能想要这样的东西:

I don't think joining group_person multiple times'll do it for you. You probably want something like this:

SELECT group_id
FROM Group_Person
WHERE person_id in(put the ids here)
GROUP BY group_id
HAVING count(group_id) = the count of person_ids

这篇关于MySQL查询类似相交的函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！