MySQL中用NOT IN语句删除多列重复记录失效问题及解决

语句失效的原因 #

1. 非标准GROUP BY语法导致结果异常：你的子查询中GROUP BY session_id, user_id, quiz_id, question_id后直接SELECT id，不符合标准SQL规范——GROUP BY的字段必须包含SELECT中所有非聚合字段。在MySQL等数据库中，若关闭ONLY_FULL_GROUP_BY模式，数据库会随机返回每组中的一个id，但这种随机返回可能导致子查询的id集合覆盖了表中所有记录的id，使得NOT IN没有匹配的待删除记录；若开启该模式，子查询会直接报错。
2. 同表子查询的执行限制：部分数据库（如MySQL）不允许在DELETE语句的WHERE子句中直接引用同一个表的子查询进行过滤，查询优化器无法正确解析这种关联，最终导致子查询返回的结果不符合预期，没有找到需要删除的记录。

解决方法 #

方法一：嵌套子查询规避同表引用限制 #

将原分组查询嵌套在一层子查询中，让数据库先完成分组逻辑，再获取有效id集合：

DELETE FROM su_submitted_answers 
WHERE `id` NOT IN (
    SELECT t.id FROM (
        SELECT id FROM su_submitted_answers 
        GROUP BY session_id, user_id, quiz_id, question_id
    ) AS t
);

方法二：窗口函数（推荐，逻辑清晰可控） #

适用于MySQL 8.0+、PostgreSQL等支持窗口函数的数据库，通过ROW_NUMBER()给每组重复记录编号，明确指定保留哪一条（比如按id升序保留最早的记录）：

DELETE FROM su_submitted_answers 
WHERE id IN (
    SELECT id FROM (
        SELECT 
            id,
            ROW_NUMBER() OVER (
                PARTITION BY session_id, user_id, quiz_id, question_id 
                ORDER BY id -- 可改为ORDER BY id DESC保留最新记录
            ) AS rn
        FROM su_submitted_answers
    ) AS t
    WHERE rn > 1
);

方法三：自连接删除 #

通过自连接找到每组需要保留的记录id（比如最小id），删除不在该集合中的记录：

DELETE s1
FROM su_submitted_answers s1
LEFT JOIN (
    SELECT MIN(id) AS keep_id -- 替换为MAX(id)可保留每组最大id的记录
    FROM su_submitted_answers
    GROUP BY session_id, user_id, quiz_id, question_id
) s2 ON s1.id = s2.keep_id
WHERE s2.keep_id IS NULL;

内容的提问来源于stack exchange，提问作者Gamer In The Game