MySQL无INTERSECT时，多条件匹配表行获取response_id的实现方案

嘿，这个问题我太熟了！MySQL确实没提供INTERSECT语法，但咱们有好几种靠谱的替代方案，完全能实现你要的逻辑。先把你提到的标准SQL框架列出来，再一步步转成MySQL能用的写法。

先看你提到的标准SQL示例框架 #

-- 场景1：用户选择了question 1的answer 1 且 question 2的answer 2
SELECT response_id FROM your_table
WHERE question_id = 1 AND answer_id = 1
INTERSECT
SELECT response_id FROM your_table
WHERE question_id = 2 AND answer_id = 2;

-- 场景2：(用户选择question 1的answer 3 且 question 2的answer 2) 或 question 3的answer 1
(SELECT response_id FROM your_table
WHERE question_id = 1 AND answer_id = 3
INTERSECT
SELECT response_id FROM your_table
WHERE question_id = 2 AND answer_id = 2)
UNION
SELECT response_id FROM your_table
WHERE question_id = 3 AND answer_id = 1;

MySQL替代方案：分场景实现 #

场景1：多条件“且”的逻辑（替代INTERSECT）

这里核心是找同时满足多个条件的response_id，推荐两种常用方法：

方法1：GROUP BY + HAVING（最简洁，推荐）

这种方法适合扩展到更多条件（比如3个、4个条件），可读性极强：

SELECT response_id
FROM your_table
WHERE 
  (question_id = 1 AND answer_id = 1) 
  OR (question_id = 2 AND answer_id = 2)
GROUP BY response_id
-- 因为要同时满足2个不同的question条件，所以计数必须等于2
HAVING COUNT(DISTINCT question_id) = 2;

注：如果同一个response_id对同一个question可能有多个答案，COUNT(DISTINCT)能避免重复计数，保证结果准确。

方法2：自连接

适合条件较少的场景，逻辑直观：

SELECT DISTINCT t1.response_id
FROM your_table t1
JOIN your_table t2 
  ON t1.response_id = t2.response_id
WHERE 
  t1.question_id = 1 AND t1.answer_id = 1
  AND t2.question_id = 2 AND t2.answer_id = 2;

通过将表自身连接，找到同时存在两个条件记录的response_id，用DISTINCT避免重复结果。

方法3：EXISTS子查询

大数据量下性能可能更优（尤其是response_id有索引时）：

SELECT DISTINCT response_id
FROM your_table t1
WHERE 
  question_id = 1 AND answer_id = 1
  AND EXISTS (
    SELECT 1 FROM your_table t2
    WHERE t2.response_id = t1.response_id
      AND t2.question_id = 2 AND t2.answer_id = 2
  );

场景2：“（且条件）或 单条件”的逻辑

这里只需要把场景1的“且条件”结果，和单条件的结果用UNION合并即可（UNION会自动去重，不需要去重用UNION ALL更快）：

用GROUP BY的方式实现：

-- 先获取满足（question1=answer3 且 question2=answer2）的response_id
SELECT response_id
FROM your_table
WHERE 
  (question_id = 1 AND answer_id = 3) 
  OR (question_id = 2 AND answer_id = 2)
GROUP BY response_id
HAVING COUNT(DISTINCT question_id) = 2

-- 合并满足question3=answer1的结果
UNION

SELECT response_id
FROM your_table
WHERE question_id = 3 AND answer_id = 1;

或者用自连接+UNION：

SELECT DISTINCT t1.response_id
FROM your_table t1
JOIN your_table t2 
  ON t1.response_id = t2.response_id
WHERE 
  t1.question_id = 1 AND t1.answer_id = 3
  AND t2.question_id = 2 AND t2.answer_id = 2

UNION

SELECT response_id
FROM your_table
WHERE question_id = 3 AND answer_id = 1;

小提示 #

• 如果你的表数据量很大，记得给response_id、question_id、answer_id加联合索引，能大幅提升查询速度；
• 若不需要去重，用UNION ALL替代UNION，性能会更好。

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