MySQL中如何基于重复查询结果删除对应max(id)的记录？

你的原DELETE语句无法工作的原因有两个：一是子查询返回了max(id)和cnt两个字段，而WHERE子句需要的是能匹配单条记录的单一条件；二是MySQL不允许在DELETE/UPDATE操作中直接引用正在修改的表作为子查询的数据源。下面给你两种可行的解决方案：

方法一：使用嵌套子查询（IN子句） #

先通过嵌套子查询获取所有需要删除的max(id)值，再匹配删除对应的记录：

DELETE FROM `table`
WHERE id IN (
    SELECT max_id FROM (
        SELECT MAX(id) AS max_id
        FROM `table`
        GROUP BY start_id, end_id, mysqltable
        HAVING COUNT(*) > 1
    ) AS temp_table
);

这里的内层子查询先筛选出每组重复记录的max(id)，然后套一层临时表（temp_table）来规避MySQL不能直接引用修改表的限制。

方法二：使用JOIN关联删除（效率更高） #

对于数据量较大的表，JOIN的方式通常比子查询更高效：

DELETE t1
FROM `table` t1
JOIN (
    SELECT MAX(id) AS max_id
    FROM `table`
    GROUP BY start_id, end_id, mysqltable
    HAVING COUNT(*) > 1
) t2 ON t1.id = t2.max_id;

这里给原表起别名t1，子查询结果起别名t2，通过JOIN关联找到需要删除的记录，直接删除t1中匹配的行。

重要提醒 #

1. 先测试再删除：执行DELETE前，建议把语句换成SELECT来验证要删除的记录是否正确，比如：

SELECT * FROM `table`
WHERE id IN (
    SELECT max_id FROM (
        SELECT MAX(id) AS max_id
        FROM `table`
        GROUP BY start_id, end_id, mysqltable
        HAVING COUNT(*) > 1
    ) AS temp_table
);

2. 备份数据：删除操作不可逆，一定要先备份目标表的数据，避免误删造成损失。
3. 关键字处理：如果你的表名table是MySQL的保留关键字，必须用反引号``包裹，否则会触发语法错误。

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