- 创建学生表
CREATE TABLE student(id INT PRIMARY KEY,stuno VARCHAR(12) NOT NULL,stuname VARCHAR(30) NOT null);
- 向学生表中插入数据
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','小李');
- 查找仅学号重复的记录
-- 学号重复
-- 先按学号进行分组,然后查询学数量 > 1的记录的学号
SELECT * FROM student WHERE stuno IN (
-- 查找重复的学号
SELECT stuno FROM student GROUP BY stuno HAVING COUNT(stuno) > 1
);
得到结果如下:
- 查找学号和姓名均重复的记录
-- 学号和姓名均重复
SELECT * FROM student WHERE (stuno,stuname) -- 注意:此处一定要加括号,当成联合字段来处理
IN (
-- 查找学号和姓名均重复的学生信息
SELECT stuno,stuname FROM student GROUP BY stuno,stuname HAVING COUNT(1) > 1
);
查询结果如下:
- 删除多余的重复记录(多个字段),只保留最小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