MySQL查询和删除重复记录

1. 创建学生表

CREATE TABLE student(id INT PRIMARY KEY,stuno VARCHAR(12) NOT NULL,stuname VARCHAR(30) NOT null);

2. 向学生表中插入数据

INSERT INTO student VALUES ('1','131111099','小李');
INSERT INTO student VALUES ('2','131111100','小陈');
INSERT INTO student VALUES ('3','131111101','小王');
INSERT INTO student VALUES ('4','131111102','小黑');
INSERT INTO student VALUES ('5','131111099','小曹');
INSERT INTO student VALUES ('6','131111099','小李');

3. 查找仅学号重复的记录

-- 学号重复
-- 先按学号进行分组，然后查询学数量 > 1的记录的学号
SELECT * FROM student WHERE stuno IN (
    -- 查找重复的学号
    SELECT stuno FROM student GROUP BY stuno HAVING COUNT(stuno) > 1
);

得到结果如下：

4. 查找学号和姓名均重复的记录

-- 学号和姓名均重复
SELECT * FROM student WHERE (stuno,stuname) -- 注意：此处一定要加括号，当成联合字段来处理
 IN (
    -- 查找学号和姓名均重复的学生信息
    SELECT stuno,stuname FROM student GROUP BY stuno,stuname HAVING COUNT(1) > 1
);

查询结果如下：

5. 删除多余的重复记录(多个字段)，只保留最小id的记录

-- 删除多余的重复记录(多个字段)，只保留最小id的记录
DELETE FROM student WHERE id IN (
    SELECT * FROM (
        SELECT id FROM student WHERE (stuno,stuname) -- 注意：此处一定要加括号，当成联合字段来处理
        IN (
            -- 查找学号和姓名均重复的学生信息
            SELECT stuno,stuname FROM student GROUP BY stuno,stuname HAVING COUNT(1) > 1
        ) AND id NOT IN (
            -- 查询最小id的记录
            SELECT MIN(id) FROM student GROUP BY stuno,stuname HAVING COUNT(1) > 1
        )
    ) AS stu_repeat_copy

);

结果如下：

警告：不能根据本表的查询结果来更新本表的数据

在其它的帖子中有看到如下写法来删除重复数据：

DELETE FROM student WHERE (stuno,stuname) -- 注意：此处一定要加括号，当成联合字段来处理
    IN (
        -- 查找学号和姓名均重复的学生信息
        SELECT stuno,stuname FROM student GROUP BY stuno,stuname HAVING COUNT(1) > 1
    ) AND id NOT IN (
        -- 查询最小id的记录
        SELECT MIN(id) FROM student GROUP BY stuno,stuname HAVING COUNT(1) > 1
);

会报如下错误

[Err] 1093 - You can’t specify target table ‘student’ for update in FROM clause