MySQL如何从存在重复值的表中查询同时满足多个值条件的ID

关于两次遍历表产生开销的说法验证 #

你提到的两次全表检索带来额外开销的说法是正确的。如果value字段没有建立专用索引，你写的自连接查询会触发两次独立的全表扫描，分别筛选value=7和value=9的记录。当需要匹配的目标value数量增加到3个、4个时，扫描次数也会对应增长，开销会线性上升。

更优的实现方案 #

推荐使用单表分组聚合的方案，仅需一次全表扫描即可完成需求，且扩展性极强：

SELECT id
FROM table_name
-- 先过滤掉非目标值的记录，减少后续聚合计算量
WHERE value IN (7, 9)
GROUP BY id
-- 统计每个id下包含的不同目标value数量，等于目标值总数即符合要求
HAVING COUNT(DISTINCT value) = 2;

方案说明 #

• 适配重复值场景：COUNT(DISTINCT value)会自动合并同一个id下重复的value记录，避免重复计数。如果你的表已经有id + value的唯一约束，可去掉DISTINCT进一步提升性能，直接写COUNT(value) = 2即可。
• 扩展性极强：如果需要查询同时包含3个、4个甚至更多value的id，仅需修改两处：
  • 调整WHERE子句中IN后的目标值列表
  • 把HAVING后等于的数字改成目标值的总数量

比如要找同时包含7、8、9的id，写法如下：

SELECT id
FROM table_name
WHERE value IN (7, 8, 9)
GROUP BY id
HAVING COUNT(DISTINCT value) = 3;

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