方法1:
; b& Y" F, K4 _1 o3 }! X/ j6 }9 q: k/ s1、创建一个临时表,选取需要的数据。; o- ?) \: a. s5 L8 O
2、清空原表。
# @9 Z# H1 a& {3 [3、临时表数据导入到原表。( Y. K2 X4 o d4 Q2 v, L3 c
4、删除临时表。4 c, \7 S2 \( F5 ~% x
mysql> select * from student;
N# R4 G5 D- S% {- s b3 N; H7 A+----+------+" V5 x2 \5 V+ n/ M" K2 H
| ID | NAME |
0 p* s. g s4 @ Y2 L+----+------+
& w1 y) g9 _9 Z8 b/ ^3 f| 11 | aa |3 V9 Z j6 _& @/ r( l+ M+ h6 t
| 12 | aa |" g& d: V0 F: I$ b* ~
| 13 | bb |- W1 f, {* I J# }: s& G
| 14 | bb |
4 p% G! y. L/ g' x1 J4 N| 15 | bb |5 G& K, b1 W0 _; o) W7 `5 \
| 16 | cc |
5 g$ | ^; M9 h |0 j4 u+----+------+: g: A# ^8 n8 a+ K
6 rows in set mysql> create temporary table temp as select min(id),name from student group by name;
( |7 J3 p# j$ v, mQuery OK, 3 rows affected/ ~- V8 \4 }, E
Records: 3 Duplicates: 0 Warnings: 0 mysql> truncate table student;
% m$ N) ]) P% @$ e2 n! A) j) WQuery OK, 0 rows affected mysql> insert into student select * from temp;! `1 F& x# M7 G. W. {; f2 ~
Query OK, 3 rows affected( K3 Z/ F+ V/ Z
Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from student;* Z4 p6 r1 ~* u. j
+----+------+7 c* g7 c: d# a! T X
| ID | NAME |
S2 Y( \& u5 c. b( H+----+------+
6 o% R+ ?& p/ o1 a| 11 | aa |" Z/ d7 w: |* }% `" |# ?! q
| 13 | bb |5 ]$ z: `$ q9 p3 h* b
| 16 | cc |
+ r, M4 E* Y( H/ h& e8 _+----+------+& |8 ?: v: }0 A5 M
3 rows in set mysql> drop temporary table temp;
* ^) N2 I( ~' Q% x ?: l) {( O2 HQuery OK, 0 rows affected/ A4 a3 l' Z: n/ J" ~& {0 a. n0 D
这个方法,显然存在效率问题。
方法2:按name分组,把最小的id保存到临时表,删除id不在最小id集合的记录,如下:/ T6 t! z4 t7 v) P* Q+ |0 x* D& h, y
mysql> create temporary table temp as select min(id) as MINID from student group by name;
5 g& {) p. t$ U2 @8 jQuery OK, 3 rows affected7 ]1 i; {9 q6 h! x! T
Records: 3 Duplicates: 0 Warnings: 0 mysql> delete from student where id not in (select minid from temp);
, G% N R) ~5 C9 U3 ]8 G" ]$ lQuery OK, 3 rows affected mysql> select * from student;! r: j8 D' s2 |. r6 l- v' @
+----+------+
- ^* m- H5 Z7 R# u| ID | NAME |
4 z! m( Z* S4 h: N- r) U# ^" M+----+------+2 Z2 E& f# p1 n4 W/ h# }
| 11 | aa |
5 c$ \8 R' c' O: G4 R| 13 | bb |
' @8 ?: I+ h2 w+ m6 ^# v" d| 16 | cc |+ a8 R# a7 r( ]6 p0 t& x$ {% s
+----+------+2 k1 `; ?+ Z1 g" E5 I$ s
3 rows in set
方法3:直接在原表上操作,容易想到的sql语句如下: mysql> delete from student where id not in (select min(id) from student group by name);
% q5 d/ N2 C5 C$ s: q& Z执行报错:1093 - You can't specify target table 'student' for update in FROM clause
1 l @( W3 V6 q- n$ p原因是:更新数据时使用了查询,而查询的数据又做了更新的条件,mysql不支持这种方式。: |. e& F$ D; ?# G: v- R* V- C/ p
怎么规避这个问题?3 r+ F1 l3 B$ |
再加一层封装,如下:- ]+ `3 _" o9 N0 u$ p! T
mysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);" D# a- Z ~( } [
Query OK, 3 rows affected mysql> select * from student;
6 g& D; t; I3 o3 H% }" x+ V+----+------+
5 a+ e4 H: m0 g( N# G s| ID | NAME |
; J) ^. ^6 o" c: P; p+----+------++ G1 Z6 c! d6 c
| 11 | aa |
- B/ A" p! w6 y3 ~0 k8 z( h| 13 | bb |
( z5 m7 S( p/ l1 N% l' H* g6 Z; s, Z| 16 | cc |4 |+ a) U O( ?( [5 m
+----+------+! S- _6 ]( e D0 f/ q' ?
3 rows in set
' v) i& e, E* o2 m; n& J3 q方法三例如:delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);
/ J: l. M0 j) g/ l6 y
; q* M/ I3 S( ?4 A5 D% e9 z! c: y) @# M7 P9 r
|