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的数据负载。

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注