MySQL如何查询单列匹配多个值的所有行 筛选同时参加两项赛事的用户

现有contest_played数据表，存储字段如下：

• userID：用户ID
• play_status：参赛状态，值为布尔类型True/False
• competitionID：赛事ID
  单个用户可参加多个赛事，需要筛选出同时参加了competitionID为5和6的用户对应的这两个赛事的所有行，未同时参与两个赛事的用户记录不能出现在结果中。

方案1：子查询匹配（写法简单易读） #

SELECT userID, play_status, competitionID
FROM contest_played
-- 仅保留赛事5、6的记录
WHERE competitionID IN (5,6)
-- 仅保留同时参加了两个赛事的用户
AND userID IN (
    SELECT userID
    FROM contest_played
    WHERE competitionID IN (5,6)
    GROUP BY userID
    -- 确保用户同时有两个赛事的记录
    HAVING COUNT(DISTINCT competitionID) = 2
);

如果表中已经设置userID+competitionID的唯一联合约束，同一个用户同一个赛事不会重复存储，可以去掉DISTINCT提升查询效率。

方案2：自连接查询（大数据量下性能更优） #

SELECT DISTINCT c.userID, c.play_status, c.competitionID
FROM contest_played c
-- 关联判断用户有赛事5的记录
INNER JOIN contest_played c5 
    ON c.userID = c5.userID AND c5.competitionID = 5
-- 关联判断用户有赛事6的记录
INNER JOIN contest_played c6 
    ON c.userID = c6.userID AND c6.competitionID = 6
WHERE c.competitionID IN (5,6);

以上两种查询返回的结果完全符合预期，会筛选出userID为3、5、8三个用户对应的competitionID为5和6的共6条记录。

内容的提问来源于stack exchange，提问作者Sam