|
方法1:( E9 }# J3 k8 y3 L, n: _# P6 f; S8 }
1、创建一个临时表,选取需要的数据。6 @5 N( \: y& F. H' G: u& A
2、清空原表。
# s7 G! j4 s; I2 w5 B; \1 ^8 f3、临时表数据导入到原表。, @7 a; ~. P/ m% S2 O0 Z3 i- f. {8 T
4、删除临时表。7 F( N* L6 G- a& B# i4 ?
mysql> select * from student;* q" f4 N, M/ W9 q9 g2 A- r+ S" B& j
+----+------+) v/ { N$ Q* y9 X% W- w1 y
| ID | NAME |3 i/ o, t; V. } q* H( c% O
+----+------+- s `; [; w; _0 S3 B
| 11 | aa |8 g* M/ W- P* t# z* A
| 12 | aa |3 I* x. d. X( h: }" K8 R
| 13 | bb |
8 Y* U3 t2 f! `& {$ h5 ~| 14 | bb |
6 c# V. q! G' B) z4 l| 15 | bb |+ [9 t: b& x# B
| 16 | cc |& K6 C' i8 g) q# y! |- s2 a
+----+------+
! G0 X. y9 g1 ?$ y3 `6 rows in set mysql> create temporary table temp as select min(id),name from student group by name;0 Y% E% Y4 X& E" C+ L6 z n
Query OK, 3 rows affected
( d$ {* P2 T6 |1 z5 t% {! qRecords: 3 Duplicates: 0 Warnings: 0 mysql> truncate table student;
N9 }0 b. K1 v8 h% oQuery OK, 0 rows affected mysql> insert into student select * from temp;' h. D1 ~8 D# O* Q
Query OK, 3 rows affected9 p# M. X" f4 ~0 [& |& d, ^8 X
Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from student;3 Q- M* Q; w8 y) B2 J
+----+------+
* r9 x9 T3 F+ W3 D( y| ID | NAME |
2 b# e" ]( {! n5 R) N1 [4 Y+----+------+0 y. b* h+ N% |: {$ W9 x
| 11 | aa |
- t7 W' P: w- @2 G2 L/ T| 13 | bb |& v9 M; d; c1 T {# s8 E9 E
| 16 | cc |" |% _( _8 P. Y
+----+------+, c2 i; _9 @% j2 E
3 rows in set mysql> drop temporary table temp; P9 e+ I: W! ]6 A( ?8 w
Query OK, 0 rows affected3 Q( ]+ i5 S0 a/ d8 c
这个方法,显然存在效率问题。
方法2:按name分组,把最小的id保存到临时表,删除id不在最小id集合的记录,如下:
; O1 w! D/ @2 l1 f. z; T& Vmysql> create temporary table temp as select min(id) as MINID from student group by name;
- d% S" X0 }; B XQuery OK, 3 rows affected
- v" P, S- R6 \- D! i0 lRecords: 3 Duplicates: 0 Warnings: 0 mysql> delete from student where id not in (select minid from temp);4 t( J9 x1 A. i5 d1 i& j9 ~
Query OK, 3 rows affected mysql> select * from student;) Q+ d" l8 Q& j3 b; v( Q- z/ ~
+----+------+
- y- `& D% z( ^* ~| ID | NAME |
" G1 c# `1 y9 L& C2 a$ I9 P+----+------+
) O+ {% W D4 u3 i8 X* X| 11 | aa |3 C" k9 [ e8 q: A2 `5 z
| 13 | bb |
: T: |$ n# X1 |9 r6 \/ n2 s| 16 | cc |
4 D, P U2 L/ c* }* G+ ~+----+------+1 x+ g' q7 h! X3 E! R6 o5 w
3 rows in set
方法3:直接在原表上操作,容易想到的sql语句如下: mysql> delete from student where id not in (select min(id) from student group by name);: p3 b6 j8 ]3 e$ w+ I2 X3 ~
执行报错:1093 - You can't specify target table 'student' for update in FROM clause
8 t9 ]$ J6 O8 _5 M- p原因是:更新数据时使用了查询,而查询的数据又做了更新的条件,mysql不支持这种方式。
8 f: ?' P% B! ~( s怎么规避这个问题?! ^* D, E v x, _$ X
再加一层封装,如下:
# z3 F% E7 e6 Z7 R) W% Gmysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);; X/ s: H1 b$ \
Query OK, 3 rows affected mysql> select * from student;# _# q% f+ o5 _6 y, p8 B9 I( v. x; {
+----+------+) x7 T! t+ ^* \9 ~* U" r$ e1 Z; |- x& T) T
| ID | NAME |: L4 ?2 N& Y; T3 _
+----+------+) h9 }: T' V/ g4 ~
| 11 | aa |' \0 n, c! y1 T) o9 }8 `- B. b
| 13 | bb |+ P7 L$ b; i/ R m9 \5 P. U! E/ a
| 16 | cc |* C0 p8 s0 E9 x6 X
+----+------+- B0 j2 Z1 W) A9 t' e
3 rows in set
. D, v: [% d$ \" j. ~( M方法三例如:delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);
& b% ?. r4 F7 p1 N( v4 L3 g9 j P
2 ]+ `, P& z- M) N& `
' y0 L W( B1 _ G& P1 a |