mysql删除重复记录，保存Id最小的一条

admin

方法1：
) S& K4 r; l+ q2 p' X6 A! S1、创建一个临时表，选取需要的数据。
- m) {( a! W. T! t) r- ?* ~6 [1 N2、清空原表。
3、临时表数据导入到原表。
& a" {0 s/ A' U4、删除临时表。
mysql> select * from student;
+----+------+
( P/ S9 }1 ^* B0 U% X| ID | NAME |
+----+------+
4 j& Z3 S4 R8 D! a7 p& N| 11 | aa |
1 N$ n7 `8 ?, l/ o| 12 | aa |
| 13 | bb |
| 14 | bb |
| 15 | bb |
' M  D" c, K. j9 {5 G| 16 | cc |
) f: ~7 d" R# H# }1 Z1 i# _4 i, Y7 E+----+------+
$ m4 b/ O  S5 n; a/ t8 S6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
Query OK, 3 rows affected
5 A( l+ _5 {' S/ CRecords: 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;
+----+------+
5 w4 u0 }" c2 `* u| ID | NAME |
1 K7 f0 S+ k( M5 b* [+----+------+
. J7 U6 P9 \" ?$ O. n* w| 11 | aa |
: R/ c" i6 \1 e* h& o; e# X9 h| 13 | bb |
3 \: E1 g) e3 {+ E/ L| 16 | cc |
+----+------+
& g! S4 U6 f% O& B3 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;
9 n8 X) o8 b( j) OQuery 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;
8 O( Z9 u, t- w# J. q7 Y) @+----+------+
* u# \. v' f4 a! a; c# n9 L  Y+ w| ID | NAME |
  R$ T  y8 ~. q% d+----+------+
| 11 | aa |
* z4 ^9 ~" l2 {5 }& |9 v| 13 | bb |
) R. o) H6 [$ x" O8 u. y| 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
" m2 S" b& B, x4 L. ^原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
怎么规避这个问题？
再加一层封装，如下：
+ P( `! t5 M# dmysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
% @7 g0 c+ ^! o% oQuery OK, 3 rows affected

mysql> select * from student;
* I" w% m0 r7 w! e+ ~+----+------+
| ID | NAME |
+----+------+
' \4 u$ D6 H8 d' `, z. z' C; M| 11 | aa |
+ l$ Y  [. K" @' ?| 13 | bb |
| 16 | cc |
+----+------+
3 rows in set

" m# o$ q$ [  h方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);


8 T2 Z1 Y' M6 `2 p! a! e