今日经验mysql删除重复数据（百度不给力）
今日经验mysql删除重复数据（百度不给力）
今天在网上找的一个面试题，是擎天科技的面试题最后一题，题目是这样的：
3.一道SQL题，先找出表中的重复的元素，然后再删除。
刚开始做这个题刚以为很简单，就拿一个简单的例子在我的mysql里试了下手，首先我新建了下面一个数据库名字为text1，新建表student,插入数据如下图所示：
+----+------+------+
| id | name | math |
+----+------+------+
| 1 | aaa | 99 |
| 2 | bbb | 98 |
| 3 | aaa | 99 |
| 4 | bbb | 88 |
| 5 | sss | 88 |
+----+------+------+
有7条数据，只有第一条和第三条是相同的重复数据。
| id | name | math |
+----+------+------+
| 1 | aaa | 99 |
| 2 | bbb | 98 |
| 3 | aaa | 99 |
| 4 | bbb | 88 |
| 5 | sss | 88 |
+----+------+------+
有7条数据，只有第一条和第三条是相同的重复数据。
首先
找出重复的元素，select * from student a where a.id!=(select max(id) from student b where a.name=b.name and a.math=b.math);
可以得出：
+----+------+------+
| id | name | math |
+----+------+------+
| 1 | aaa | 99 |
+----+------+------+
明显非常正确。（但后来发现如果有数据重复3次或3次以上没意义了
| id | name | math |
+----+------+------+
| 1 | aaa | 99 |
+----+------+------+
明显非常正确。（但后来发现如果有数据重复3次或3次以上没意义了
就必须用如下语句：select name,math,count(*) from student group by name,math having count(*)>1;）
第二步，删除重复数据，delete from student a where a.id!=(select max(id) from student b where a.name=b.name and a.math=b.math);
但是执行的时候，就出错了：
在一个答案里我看到了一句话：mysql不支持对同一个表查询后执行修改操作。这样我就想到了建临时表。具体步骤如下（数据经过调整）：+----+------+------+
| id | name | math |
+----+------+------+
| 1 | aaa | 99 |
| 2 | bbb | 98 |
| 3 | aaa | 99 |
| 4 | bbb | 88 |
| 5 | sss | 88 |
| 6 | aaa | 99 |
| 7 | ccc | 99 |
+----+------+------+
| id | name | math |
+----+------+------+
| 1 | aaa | 99 |
| 2 | bbb | 98 |
| 3 | aaa | 99 |
| 4 | bbb | 88 |
| 5 | sss | 88 |
| 6 | aaa | 99 |
| 7 | ccc | 99 |
+----+------+------+
mysql> create temporary table s select * from student group by name,math;
Query OK, 5 rows affected (0.19 sec)
Records: 5 Duplicates: 0 Warnings: 0
Query OK, 5 rows affected (0.19 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from s;(检查一下是否是需要的数据）
+----+------+------+
| id | name | math |
+----+------+------+
| 1 | aaa | 99 |
| 4 | bbb | 88 |
| 2 | bbb | 98 |
| 7 | ccc | 99 |
| 5 | sss | 88 |
+----+------+------+
5 rows in set (0.00 sec)
+----+------+------+
| id | name | math |
+----+------+------+
| 1 | aaa | 99 |
| 4 | bbb | 88 |
| 2 | bbb | 98 |
| 7 | ccc | 99 |
| 5 | sss | 88 |
+----+------+------+
5 rows in set (0.00 sec)
（明显对了）然后进行表student数据的清除：
mysql> delete from student;
Query OK, 7 rows affected (0.03 sec)
Query OK, 7 rows affected (0.03 sec)
mysql> select * from student;
Empty set (0.00 sec)
Empty set (0.00 sec)
然后，再把临时表的数据写入表表student中：
mysql> insert into student(id,name,math) select id,name,math from s;
Query OK, 5 rows affected (0.02 sec)
Records: 5 Duplicates: 0 Warnings: 0
Query OK, 5 rows affected (0.02 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from student;
+----+------+------+
| id | name | math |
+----+------+------+
| 1 | aaa | 99 |
| 2 | bbb | 98 |
| 4 | bbb | 88 |
| 5 | sss | 88 |
| 7 | ccc | 99 |
+----+------+------+
5 rows in set (0.00 sec)
+----+------+------+
| id | name | math |
+----+------+------+
| 1 | aaa | 99 |
| 2 | bbb | 98 |
| 4 | bbb | 88 |
| 5 | sss | 88 |
| 7 | ccc | 99 |
+----+------+------+
5 rows in set (0.00 sec)
这样就实现MYSQL删除表内重复数据的功能了。
yuanbin 
Ulquiorra 
fanlix 
quguiliang