方法1:6 }2 G0 S4 ?, p9 f. k, k$ O
1、创建一个临时表,选取需要的数据。
9 n# \4 B+ ~% v6 f2、清空原表。
7 @: \; Q8 x* \3、临时表数据导入到原表。) D5 a( U3 y1 v7 t- ]' Y
4、删除临时表。
0 t* @% D! B( wmysql> select * from student;5 ` U$ W- b+ N7 }
+----+------+
3 I5 W% z$ n$ e7 p) [% C| ID | NAME |
7 j$ P9 Q6 c' Y1 f- w+----+------+
8 Q$ {" g( q( ^' C| 11 | aa |
0 @- e6 a0 D6 t9 b/ G* @+ _! e| 12 | aa |
% I: _# _9 \7 ]# u6 G| 13 | bb |$ _8 T" ^: U0 }+ L* g
| 14 | bb |4 c0 {, t0 f {2 j+ b# z# o3 m
| 15 | bb |
: e+ r H F0 D' Y. S| 16 | cc |& z* q: ~- v" I9 F- A7 E* i w
+----+------+
4 N5 K! F; Z. L& {. w6 rows in set mysql> create temporary table temp as select min(id),name from student group by name;# x \& C; Q; o8 H* x# I
Query OK, 3 rows affected' X5 c$ s: U8 C% ^9 v; x
Records: 3 Duplicates: 0 Warnings: 0 mysql> truncate table student;, Z1 R/ {7 V7 R) M L, Q
Query OK, 0 rows affected mysql> insert into student select * from temp;
/ F5 k4 M# T* v9 v5 `* D! c1 NQuery OK, 3 rows affected
- b# C; s, q4 B6 n6 aRecords: 3 Duplicates: 0 Warnings: 0 mysql> select * from student;. ]1 t8 F$ j! i2 i( o" J* \
+----+------+# U- a; }, o/ I# d
| ID | NAME |
8 ]. g! D8 u9 n$ b% {+----+------+
; {0 U$ ?5 B& n9 e. T| 11 | aa |) |1 j# m; s0 Z: t9 w J6 s
| 13 | bb |6 h( c% x j% _/ Q" J& {4 [, P
| 16 | cc |
7 M# p' F9 E0 Y+----+------+
; u: U* Q4 j7 A ^5 }3 rows in set mysql> drop temporary table temp;3 M6 ^/ h. w' u% I8 l: w9 c) d
Query OK, 0 rows affected9 n: u g# E' h, T& r/ k9 B; i! w* `
这个方法,显然存在效率问题。
方法2:按name分组,把最小的id保存到临时表,删除id不在最小id集合的记录,如下:' ^- @+ b9 M5 q% M
mysql> create temporary table temp as select min(id) as MINID from student group by name;
, I0 o: D# i* `8 {Query OK, 3 rows affected
, x( J$ Q: c: w6 @! RRecords: 3 Duplicates: 0 Warnings: 0 mysql> delete from student where id not in (select minid from temp);+ ?0 w6 l. }5 X- b
Query OK, 3 rows affected mysql> select * from student;
! X ~9 l8 ?5 r+----+------+
1 e9 }( M6 {3 ?; `4 T6 b8 I| ID | NAME |* A- L% q& U& `' _! O7 Y7 ]
+----+------+
+ o$ W" R- M7 ^: m) c/ x& p| 11 | aa |3 \1 }4 {+ l. A3 x" v0 s: G- O# c
| 13 | bb |
( G; k- r0 ?3 r. b| 16 | cc |
; ]2 x4 K, X* R1 K' D7 C- d+----+------+) m: E# k9 A* L
3 rows in set
方法3:直接在原表上操作,容易想到的sql语句如下: mysql> delete from student where id not in (select min(id) from student group by name);
( n# {% ^3 q0 W4 \: j/ K执行报错:1093 - You can't specify target table 'student' for update in FROM clause
6 q# W+ A& q4 I" Z+ s原因是:更新数据时使用了查询,而查询的数据又做了更新的条件,mysql不支持这种方式。4 t- A* y, `! ~8 @5 E
怎么规避这个问题?
) w1 r$ s; `" j* H! U再加一层封装,如下:
8 h: N3 M5 R( F' M$ V9 f! wmysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);+ \' }* F0 l; s6 v/ |! K( K7 H
Query OK, 3 rows affected mysql> select * from student;) R) g1 @+ c- l' e! e
+----+------+- k. V, h9 [$ { {0 P" h
| ID | NAME |: {" }8 ~; I9 E. a7 x' q
+----+------+: D- p; W) {' f) s8 M
| 11 | aa |; _% @- J9 M. g7 c* z0 N' K
| 13 | bb |8 J" K) C, U S; T! f
| 16 | cc |! S5 ^8 [3 D6 t: ]
+----+------+
# i% D% ~5 T7 h& ~+ i3 rows in set
: t7 L- q4 G. e( G% u方法三例如:delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);1 @* t( X- k3 `' k: G
2 Y" v& b3 d1 o: C% m
! ~' x; Y" O$ k |