删除前的person表(删除name值相同的记录,保留id最小的记录)
+----+-------+------+------+
| id | name | job | age |
+----+-------+------+------+
| 1 | Mike | 18 | 30 |
| 2 | Tom | 16 | 25 |
| 3 | Rose | 16 | 23 |
| 4 | James | 16 | 38 |
| 5 | Alex | 16 | 48 |
| 6 | Lin | 18 | 26 |
| 7 | Yao | 18 | 35 |
| 8 | Zhang | 17 | 35 |
| 9 | Wang | 17 | 45 |
| 10 | Li | 17 | 25 |
| 11 | Cury | NULL | 30 |
| 12 | Tom | 16 | 21 |
| 13 | Tom | 18 | 29 |
| 14 | Mike | 17 | 19 |
+----+-------+------+------+
mysql> DELETE t1 FROM person AS t1 LEFT JOIN (SELECT * FROM person GROUP BY name
HAVING COUNT(name)>=2) AS t2 ON t1.name=t2.name WHERE t1.id>t2.id;
Query OK, 3 rows affected (0.20 sec)
删除后的person表:
+----+-------+------+------+
| id | name | job | age |
+----+-------+------+------+
| 1 | Mike | 18 | 30 |
| 2 | Tom | 16 | 25 |
| 3 | Rose | 16 | 23 |
| 4 | James | 16 | 38 |
| 5 | Alex | 16 | 48 |
| 6 | Lin | 18 | 26 |
| 7 | Yao | 18 | 35 |
| 8 | Zhang | 17 | 35 |
| 9 | Wang | 17 | 45 |
| 10 | Li | 17 | 25 |
| 11 | Cury | NULL | 30 |
+----+-------+------+------+