MySQL查询需求：从ttquestion筛选排除指定用户已做的12条分难度题目

直接针对每个难度单独筛选排除用户已做过的题目，再随机取数合并即可，以下提供两种实现方式：

方式一：使用NOT IN排除已做题目 #

SELECT * FROM (
    SELECT qId, question, option1, option2, option3, difficulty
    FROM ttquestion
    WHERE difficulty = 1
      AND qId NOT IN (SELECT qId FROM level WHERE userId = @target_user)
    ORDER BY RAND()
    LIMIT 4
) AS diff1
UNION ALL
SELECT * FROM (
    SELECT qId, question, option1, option2, option3, difficulty
    FROM ttquestion
    WHERE difficulty = 2
      AND qId NOT IN (SELECT qId FROM level WHERE userId = @target_user)
    ORDER BY RAND()
    LIMIT 4
) AS diff2
UNION ALL
SELECT * FROM (
    SELECT qId, question, option1, option2, option3, difficulty
    FROM ttquestion
    WHERE difficulty = 3
      AND qId NOT IN (SELECT qId FROM level WHERE userId = @target_user)
    ORDER BY RAND()
    LIMIT 4
) AS diff3
ORDER BY difficulty;

• 把@target_user替换成你实际要指定的用户ID
• 每个子查询单独处理一个难度，用NOT IN排除该用户在level表中已关联的题目ID
• 子查询内用ORDER BY RAND()随机打乱，LIMIT 4取4条
• 最后用UNION ALL合并结果，外层按难度排序保证整体顺序

方式二：使用LEFT JOIN排除已做题目 #

如果level表数据量较大，LEFT JOIN的性能可能更优：

SELECT * FROM (
    SELECT t.qId, t.question, t.option1, t.option2, t.option3, t.difficulty
    FROM ttquestion t
    LEFT JOIN level l ON t.qId = l.qId AND l.userId = @target_user
    WHERE t.difficulty = 1 AND l.qId IS NULL
    ORDER BY RAND()
    LIMIT 4
) AS diff1
UNION ALL
SELECT * FROM (
    SELECT t.qId, t.question, t.option1, t.option2, t.option3, t.difficulty
    FROM ttquestion t
    LEFT JOIN level l ON t.qId = l.qId AND l.userId = @target_user
    WHERE t.difficulty = 2 AND l.qId IS NULL
    ORDER BY RAND()
    LIMIT 4
) AS diff2
UNION ALL
SELECT * FROM (
    SELECT t.qId, t.question, t.option1, t.option2, t.option3, t.difficulty
    FROM ttquestion t
    LEFT JOIN level l ON t.qId = l.qId AND l.userId = @target_user
    WHERE t.difficulty = 3 AND l.qId IS NULL
    ORDER BY RAND()
    LIMIT 4
) AS diff3
ORDER BY difficulty;

• 核心逻辑是通过LEFT JOIN关联用户已做题目，筛选出没有匹配记录（l.qId IS NULL）的题目，即用户未做过的题
• 其余逻辑和方式一一致，同样替换@target_user为实际用户ID

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