mysql刪除重複記錄，儲存Id最小的一條

方法1：
1、建立一個臨時表，選取需要的資料。
2、清空原表。
3、臨時表資料匯入到原表。
4、刪除臨時表。
mysql> select * from student;
+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
| 12 | aa |
| 13 | bb |
| 14 | bb |
| 15 | bb |
| 16 | cc |
+----+------+
6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
Query OK, 0 rows affected

mysql> insert into student select * from temp;
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
| 13 | bb |
| 16 | cc |
+----+------+
3 rows in set

mysql> drop temporary table temp;
Query OK, 0 rows affected
這個方法，顯然存在效率問題。

---

方法2：按name分組，把最小的id儲存到臨時表，刪除id不在最小id集合的記錄，如下：
mysql> create temporary table temp as select min(id) as MINID from student group by name;
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
Query OK, 3 rows affected

mysql> select * from student;
+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
| 13 | bb |
| 16 | cc |
+----+------+
3 rows in set 

---

方法3：直接在原表上操作，容易想到的sql語句如下：

mysql> delete from student where id not in (select min(id) from student group by name);
執行報錯：1093 - You can't specify target table 'student' for update in FROM clause
原因是：更新資料時使用了查詢，而查詢的資料又做了更新的條件，mysql不支援這種方式。
怎麼規避這個問題？
再加一層封裝，如下：
mysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
Query OK, 3 rows affected

mysql> select * from student;
+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
| 13 | bb |
| 16 | cc |
+----+------+
3 rows in set