|
方法1:+ e U: l& @# s/ ?5 A2 v
1、创建一个临时表,选取需要的数据。" F4 G, g6 ?0 _( E1 _7 z
2、清空原表。7 I8 s' v5 f, T4 U& d2 g. u- K
3、临时表数据导入到原表。3 D2 e3 z2 n( b t
4、删除临时表。
, J* q2 p, E; f5 Q: X% umysql> select * from student;+ h! i# [8 [8 I+ O j% M: m# J
+----+------+! @( r V1 [& m5 C Q
| ID | NAME |4 C6 M! G6 J9 @, }. @
+----+------+- C, O0 m6 \5 Y% {0 f, ~9 W& p9 Q
| 11 | aa |4 p/ b# c8 E3 j
| 12 | aa |- _0 I) w5 p3 ]. `9 T7 O/ o
| 13 | bb |/ D& P8 S) B) m0 g% A+ e& u
| 14 | bb |# B8 N( {: d/ {# @: v
| 15 | bb |' m1 ?$ r% s0 }4 j6 P/ |
| 16 | cc |$ K" e/ F' m. S+ F4 C: d1 K. p
+----+------+
* j2 _8 O1 v. Y2 M% X: x5 H6 rows in set mysql> create temporary table temp as select min(id),name from student group by name;
2 A" m6 U, N# \: [$ d3 |Query OK, 3 rows affected
$ K8 h: x- f! x. y0 Y; L* h; zRecords: 3 Duplicates: 0 Warnings: 0 mysql> truncate table student;! r. l3 S( ~9 V
Query OK, 0 rows affected mysql> insert into student select * from temp;1 C; `* D% ]0 Z3 C9 b
Query OK, 3 rows affected) s: C9 f7 p, ~
Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from student;2 o6 V7 a% M4 ^8 g- b7 }
+----+------+
: G c: [& d( ?" }* C" G- r| ID | NAME |
: W6 T# H' P. e: w+----+------+( V9 Y2 k* a1 I) Y( e+ S
| 11 | aa |0 Q* k% I3 p+ \! O/ w, M$ H5 ~1 D
| 13 | bb |. J8 d7 I |* B6 a1 ~% ^
| 16 | cc |) w9 K9 b5 K4 W; [$ a. f
+----+------+1 u& t9 d( x3 `& Y' y, ~4 y. L
3 rows in set mysql> drop temporary table temp;
3 @. w' ^8 }3 w( e0 n0 x' l( _/ EQuery OK, 0 rows affected
. F( m p0 a- _6 n; @这个方法,显然存在效率问题。
方法2:按name分组,把最小的id保存到临时表,删除id不在最小id集合的记录,如下:
% x7 N: s) M |" T6 Pmysql> create temporary table temp as select min(id) as MINID from student group by name;' G) z3 o- W* b1 v* v# n8 F
Query OK, 3 rows affected( ]8 T! H! B+ g
Records: 3 Duplicates: 0 Warnings: 0 mysql> delete from student where id not in (select minid from temp);
" `& k3 o* {% cQuery OK, 3 rows affected mysql> select * from student;/ M K) D7 A( N. T$ w; ^
+----+------+1 w& ^0 w! J( g8 N" E
| ID | NAME |; @* d$ g2 K. U* L3 C
+----+------+4 L7 L" n; e4 N& S% i5 f; a& Q( d
| 11 | aa |: f$ }& v" B4 Q7 V7 @
| 13 | bb |
$ A0 d. v+ l- l, }) n| 16 | cc |2 v0 ?: C3 D. |/ C9 n0 C+ t
+----+------+
* S1 Y0 [/ w7 j: r# a6 a. \, P" I5 E. J3 rows in set
方法3:直接在原表上操作,容易想到的sql语句如下: mysql> delete from student where id not in (select min(id) from student group by name);
! ]- e; P( g5 B) g执行报错:1093 - You can't specify target table 'student' for update in FROM clause$ G! M+ ^: Y I/ \8 _ K, H! q* o+ @
原因是:更新数据时使用了查询,而查询的数据又做了更新的条件,mysql不支持这种方式。$ U1 Q& b+ _3 ?
怎么规避这个问题?( N8 i9 |) N5 I( ~2 o2 x9 S
再加一层封装,如下:
/ ]9 x5 c: C6 omysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
& s" k& e1 A! }, _9 C! oQuery OK, 3 rows affected mysql> select * from student;
3 }8 M3 @+ v' r' D1 E" T+----+------+8 J- e% J# s; T/ f$ o) `
| ID | NAME |0 V" {3 B9 j- d, ^4 [* m" m
+----+------+
2 S- Y2 n% }) s' @( E| 11 | aa |
7 v( O7 l4 h* b| 13 | bb | v1 G( a" X0 p9 u$ s: c3 F
| 16 | cc |
% z7 U. K% g u6 G+ s+----+------+3 f j! D% X2 f% T3 M5 C
3 rows in set + d7 O+ F g g j
方法三例如:delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);
1 r4 }9 m4 V& z! y- F: e( _
+ w' r3 z4 ]- | E1 O; x/ }$ @6 l2 s; i5 v
|