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 setmysql> create temporary table temp as select min(id),name from student group by name;Query OK, 3 rows affectedRecords: 3 Duplicates: 0 Warnings: 0mysql> truncate table student;Query OK, 0 rows affectedmysql> insert into student select * from temp;Query OK, 3 rows affectedRecords: 3 Duplicates: 0 Warnings: 0mysql> select * from student;+----+------+| ID | NAME |+----+------+| 11 | aa || 13 | bb || 16 | cc |+----+------+3 rows in setmysql> 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 affectedRecords: 3 Duplicates: 0 Warnings: 0mysql> delete from student where id not in (select minid from temp);Query OK, 3 rows affectedmysql> 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 affectedmysql> select * from student;+----+------+| ID | NAME |+----+------+| 11 | aa || 13 | bb || 16 | cc |+----+------+3 rows in sethttp://www.cnblogs.com/nzbbody/p/4470638.html

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