MySQL多对多表查询：筛选仅访问指定全部user_id2的user_id1

我来帮你搞定这个需求！你要找的是只访问过30、40、50这三个用户，并且这三个用户都被访问过的user_id1集合对吧？先说说你原来的SQL哪里出问题了，再给你靠谱的解决方案。

你的原SQL问题分析 #

你原来写的条件：

WHERE t.user_id2 in (select distinct t.user_id2 from viewers t WHERE t.user_id2 = 30) 
AND t.user_id2 in (select distinct t.user_id2 from viewers t WHERE t.user_id2 = 40) 
AND t.user_id2 in (select distinct t.user_id2 from viewers t WHERE t.user_id2 = 50)

这个逻辑是错的——它要求某一行的user_id2同时等于30、40、50，但一行只能有一个user_id2值，所以这个条件永远匹配不到任何记录，自然得不到结果。

正确的解决方案 #

这里提供两种常用的写法，都能满足你的需求：

方法一：GROUP BY + HAVING（推荐，性能较好） #

这种方式通过分组统计来验证条件，逻辑清晰：

SELECT user_id1
FROM viewers
GROUP BY user_id1
HAVING 
  -- 确保30、40、50每个都至少被访问过一次
  SUM(CASE WHEN user_id2 = 30 THEN 1 ELSE 0 END) >= 1
  AND SUM(CASE WHEN user_id2 = 40 THEN 1 ELSE 0 END) >= 1
  AND SUM(CASE WHEN user_id2 = 50 THEN 1 ELSE 0 END) >= 1
  -- 确保没有访问过目标集合之外的任何用户
  AND SUM(CASE WHEN user_id2 NOT IN (30, 40, 50) THEN 1 ELSE 0 END) = 0;

逻辑解释：

• 前三个SUM(CASE...)：分别统计每个目标用户被访问的次数，只要次数≥1，就说明该user_id1访问过这个用户
• 最后一个SUM(CASE...)：统计访问过非目标用户的次数，等于0就说明没有访问过其他用户

方法二：子查询组合（逻辑更直观） #

通过多个子查询来筛选出符合条件的user_id1：

SELECT DISTINCT user_id1
FROM viewers
-- 确保访问过30
WHERE user_id1 IN (SELECT user_id1 FROM viewers WHERE user_id2 = 30)
-- 确保访问过40
AND user_id1 IN (SELECT user_id1 FROM viewers WHERE user_id2 = 40)
-- 确保访问过50
AND user_id1 IN (SELECT user_id1 FROM viewers WHERE user_id2 = 50)
-- 排除访问过其他用户的
AND user_id1 NOT IN (SELECT user_id1 FROM viewers WHERE user_id2 NOT IN (30, 40, 50));

逻辑解释：

• 前三个IN子查询：逐步筛选出访问过所有三个目标用户的user_id1
• 最后一个NOT IN子查询：把那些访问过非目标用户的user_id1排除掉

验证你的示例数据 #

用你的示例数据测试，两种方法都会返回user_id1为1和11，完全符合需求：

• 1：访问了30、40、50，无其他记录
• 11：访问了30、40、50，无其他记录
• 其他用户要么没访问全三个目标，要么访问了额外用户，都会被排除

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