数据库SQL面试题：删除除了自动编号不同, 其他都相同的学生冗余信息，去掉重复

本文根据网上常见的SQL面试题，删除除了自动编号不同, 其他都相同的学生冗余信息，给出分析和在GBase 8a数据库集群上的执行结果。

题目

已知数据如下

gbase> select * from exam_2;
+------+------------+--------+----------+-----------+-------+
| id   | student_id | name   | class_Id | classname | score |
+------+------------+--------+----------+-----------+-------+
|    1 |    2005001 | 张三   | 0001     | 数学      |    69 |
|    2 |    2005002 | 李四   | 0001     | 数学      |    89 |
|    3 |    2005001 | 张三   | 0001     | 数学      |    69 |
+------+------------+--------+----------+-----------+-------+
3 rows in set (Elapsed: 00:00:00.01)

删除除了自动编号不同, 其他都相同的学生冗余信息。

分析

从数据看，id是自动编号，没有重复，而张三是存在重复的。但我们不能认定只有一次重复，答案里应考虑到出现多行重复的场景。

按照一般去重的方法，可以拿到最大或最小的ID,然后将其它列相同，但id不同的删除。

答案

通过not in删除

如下是先拿到其他列分组后，最大的编号 max(id)，用min(id)也是可以的。然后将不属于这个集合的数据都删掉。

gbase> delete from exam_2 where id not in (select max(id) from exam_2 group by student_id,name,class_id,classname,score);             Query OK, 1 row affected (Elapsed: 00:00:00.15)

gbase> select * from exam_2;
+------+------------+--------+----------+-----------+-------+
| id   | student_id | name   | class_Id | classname | score |
+------+------------+--------+----------+-----------+-------+
|    2 |    2005002 | 李四   | 0001     | 数学      |    89 |
|    3 |    2005001 | 张三   | 0001     | 数学      |    69 |
+------+------------+--------+----------+-----------+-------+
2 rows in set (Elapsed: 00:00:00.00)

关联删除

该方法是先拿到有重复值的max(id),然后通过多表关联删除的方法，将其它字段相等，但id小于max(id)的行删除。

gbase> select student_id,name,class_id,classname,score,max(id) from exam_2 group by student_id,name,class_id,classname,score having count(*) >1;
+------------+--------+----------+-----------+-------+---------+
| student_id | name   | class_id | classname | score | max(id) |
+------------+--------+----------+-----------+-------+---------+
|    2005001 | 张三   | 0001     | 数学      |    69 |       3 |
+------------+--------+----------+-----------+-------+---------+
1 row in set (Elapsed: 00:00:00.05)

如下是完整SQL

gbase> delete a from exam_2 a inner join (select student_id,name,class_id,classname,score,max(id) maxid from exam_2 group by student_id,name,class_id,classname,score having count(*) >1)b on a.student_id=b.student_id and a.name=b.name and a.class_id=b.class_id and a.classname=b.classname and a.score=b.score and a.id<b.maxid;
Query OK, 1 row affected (Elapsed: 00:00:00.17)
gbase> select * from exam_2;
+------+------------+--------+----------+-----------+-------+
| id   | student_id | name   | class_Id | classname | score |
+------+------------+--------+----------+-----------+-------+
|    2 |    2005002 | 李四   | 0001     | 数学      |    89 |
|    3 |    2005001 | 张三   | 0001     | 数学      |    69 |
+------+------------+--------+----------+-----------+-------+
2 rows in set (Elapsed: 00:00:00.00)

总结

方法1语法简单容易理解，方法2语法复杂。但都是通过join的方式，将不匹配的删除。

在重复数据的比例极低时，比如1000用户万行里只有100用户重复的，则方法2可以减少一些join的数据负载。