MySQL查询：获取John与他人共同参与的所有测验结果

要满足需求，核心是先筛选出同时包含John和至少一位其他用户的测验ID，再取出这些测验的全部记录。以下是几种可行的SQL写法：

方法一：嵌套子查询筛选符合条件的测验ID #

SELECT q.*
FROM quizzes q
WHERE q.quiz_id IN (
    -- 先筛选John参与过的测验，再从中找出用户数>1的测验
    SELECT quiz_id
    FROM quizzes
    WHERE quiz_id IN (SELECT quiz_id FROM quizzes WHERE username = 'John')
    GROUP BY quiz_id
    HAVING COUNT(DISTINCT username) > 1
);

方法二：分组验证+关联查询 #

SELECT q.*
FROM quizzes q
JOIN (
    -- 直接分组验证：必须包含John，且总用户数>1
    SELECT quiz_id
    FROM quizzes
    GROUP BY quiz_id
    HAVING SUM(CASE WHEN username = 'John' THEN 1 ELSE 0 END) = 1
       AND COUNT(DISTINCT username) > 1
) valid_quizzes ON q.quiz_id = valid_quizzes.quiz_id;

方法三：双重EXISTS条件判断 #

SELECT q.*
FROM quizzes q
-- 确保测验有John参与
WHERE EXISTS (
    SELECT 1 FROM quizzes q2 WHERE q2.quiz_id = q.quiz_id AND q2.username = 'John'
)
-- 确保测验有其他用户参与
AND EXISTS (
    SELECT 1 FROM quizzes q3 WHERE q3.quiz_id = q.quiz_id AND q3.username != 'John'
);

说明 #

这三种写法都能精准筛选出符合要求的记录：

• 排除了只有John参与的测验（通过COUNT>1或第二个EXISTS判断）
• 排除了John未参与的测验（通过第一个子查询或SUM=1/第一个EXISTS判断）
• 最终返回这些合格测验的全部用户记录，而不只是John的。

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