使用多对多表MySQL进行过滤 [英] Filter using a many-to-many table MySQL

问题描述

我有下面的表（为简单起见，省略其他表）。 1是为所有的人，2为那些人玩的运动。我正在使用PHP来允许用户看到一个人的名单。他们可以过滤一个人的名字，或者他们所玩的运动。所以，我希望能够看到所有打棒球和足球的人。

  create table people（
 id int，
 name varchar（50）
）; 
 
创建表people_to_sports（
 personID int，
 sportID int，
主键（personID，sportID）
）; 
  

基本上我的问题是，我怎样才能使用people_to_sports来得到所有玩运动的人1和运动2例如？

我有一个sqlfiddle >这里。

谢谢！

解决方案

 SELECT 
 personID 
 FROM 
 people_to_sports 
 WHERE 
 sportID IN（1，2）
 GROUP BY 
 personID 
 HAVING 
 COUNT（*）= 2 
  

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.

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?

I have a sqlfiddle here.

Thank you!

解决方案

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

这篇关于使用多对多表MySQL进行过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！