SQL多对多关系下如何查询同时匹配两个指定字段值的记录

已知条件 #

游戏-类型关联表字段说明：

• game：存储游戏名称
• genre：存储游戏对应的单个类型标签
  示例数据对应关系：
• Void City：关联horror、survival
• Paranoid：关联survival、thriller
• Circle beat：关联action、horror
  需求：返回同时绑定action、horror两个类型的游戏，预期结果仅为Circle beat。

推荐实现（分组统计法） #

假设表名为game_genre，通用查询语句如下：

SELECT game
FROM game_genre
WHERE genre IN ('action', 'horror')
GROUP BY game
HAVING COUNT(DISTINCT genre) = 2;

优化提示：如果表中已经做了game+genre的联合唯一约束（即同一个游戏不会重复绑定同一个类型），可以把COUNT(DISTINCT genre)替换为COUNT(*)，查询性能更高。

逻辑拆解 #

1. 先用WHERE子句过滤掉所有不属于action、horror两个目标类型的记录，缩小计算范围
2. 按game字段分组，将同一个游戏的所有类型关联记录聚合
3. 通过HAVING子句筛选分组后，恰好命中2个目标类型的游戏——仅绑定单个目标类型、或者没绑定全目标类型的游戏都会被过滤，最终只返回同时满足两个类型要求的结果。

固定双类型匹配的可选写法 #

如果只需要固定匹配两个类型，也可以用自连接实现：

SELECT a.game
FROM game_genre a
INNER JOIN game_genre b
  ON a.game = b.game
WHERE a.genre = 'action'
  AND b.genre = 'horror';

这种写法在匹配类型数量增加时需要叠加多次表连接，代码冗余度高，仅适合固定双类型的简单场景，通用性不如分组统计写法。

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