使用多对多表MySQL进行筛选

I have the following tables (other tables ommitted for simplicity). 1 is for all of the people, 2 is for the sports that those people play. I am using php to allow a user to see a list of people. They can filter by a person's name, or by the sports they play. So, I want the ability to see all people that play for example baseball and football.

我有下面的表(为了简单起见而省略的其他表)。1代表所有人，2代表那些人进行的运动。我使用php允许用户查看人员列表。他们可以通过一个人的名字，或者他们的运动来过滤。所以，我希望能看到所有的人，比如棒球和足球。

create table people (
  id int,
  name varchar(50)
  );

create table people_to_sports (
  personID int,
  sportID int,
  primary key(personID,sportID)
  );

Basically my question is, how can I use people_to_sports to get a list of all people that play sport 1 and sport 2 for example?

基本上我的问题是，我如何使用people_to_sports来获取所有参加体育1和体育2的人的列表?

I have a sqlfiddle here.

这里有一个sqlfiddle。

Thank you!

谢谢你！

2 个解决方案

#1

SELECT
  personID
FROM
  people_to_sports
WHERE
  sportID IN (1, 2)
GROUP BY
  personID
HAVING
  COUNT(*) = 2

#2

SELECT personID, COUNT(personID) AS cnt
FROM people_to_sports
WHERE sportID IN (1, 2)
GROUP BY personID
HAVING cnt = 2