1 CREATE TABLE student(
2     id INT PRIMARY KEY,
3     stuno VARCHAR(12) NOT NULL,
4     stuname VARCHAR(30) NOT null
5 );

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

1 -- 學號重復
2 -- 先按學號進行分組，然后查詢學數量 > 1的記錄的學號
3 SELECT * FROM student WHERE stuno IN (
4     -- 查找重復的學號
5     SELECT stuno FROM student GROUP BY stuno HAVING COUNT(stuno) > 1
6 );

1 -- 學號和姓名均重復
2 SELECT * FROM student WHERE (stuno,stuname) -- 注意：此處一定要加括號，當成聯合字段來處理
3  IN (
4     -- 查找學號和姓名均重復的學生信息
5     SELECT stuno,stuname FROM student GROUP BY stuno,stuname HAVING COUNT(1) > 1
6 );

 1 -- 刪除多余的重復記錄(多個字段)，只保留最小id的記錄
 2 DELETE FROM student WHERE id IN (
 3     SELECT * FROM (
 4         SELECT id FROM student WHERE (stuno,stuname) -- 注意：此處一定要加括號，當成聯合字段來處理
 5         IN (
 6             -- 查找學號和姓名均重復的學生信息
 7             SELECT stuno,stuname FROM student GROUP BY stuno,stuname HAVING COUNT(1) > 1
 8         ) AND id NOT IN (
 9             -- 查詢最小id的記錄
10             SELECT MIN(id) FROM student GROUP BY stuno,stuname HAVING COUNT(1) > 1
11         )
12     ) AS stu_repeat_copy
13     
14 );

1 DELETE FROM student WHERE (stuno,stuname) -- 注意：此處一定要加括號，當成聯合字段來處理
2     IN (
3         -- 查找學號和姓名均重復的學生信息
4         SELECT stuno,stuname FROM student GROUP BY stuno,stuname HAVING COUNT(1) > 1
5     ) AND id NOT IN (
6         -- 查詢最小id的記錄
7         SELECT MIN(id) FROM student GROUP BY stuno,stuname HAVING COUNT(1) > 1
8 );