中国网络渗透测试联盟
标题: mysql删除重复记录，保存Id最小的一条 [打印本页]
作者: admin 时间: 2016-8-23 20:58
标题: mysql删除重复记录，保存Id最小的一条
方法1：
i+ C s9 a, b) F& @* c3 L1、创建一个临时表，选取需要的数据。
C0 @& a" X# X1 Z |8 e$ K; d2、清空原表。' i5 u, g ]4 {. i; ?1 j
3、临时表数据导入到原表。4 O t' q) H M$ R; v* }
4、删除临时表。1 U; @* \4 _4 [ Y) D7 S2 ]
mysql> select * from student;6 i, H2 o( ^. a' d8 {: Y @
+----+------+
7 f& Y- P) h: ]" M; w) E| ID | NAME |( ?1 K2 ~- p, d
+----+------+9 g4 m1 C; C: O; l6 b; Q( `2 j* a
| 11 | aa |
$ @5 `1 ~& r( u; F1 `3 x! T+ {: c| 12 | aa |
( ?$ _% l$ n4 y0 [* g| 13 | bb |6 i, A5 F) O( ]& I }; t
| 14 | bb |
& \5 T: D% q! d, c- Y6 R8 y| 15 | bb |- J+ m2 i2 C: R$ y5 g
| 16 | cc |
: q) [8 w6 R4 j+----+------+" o1 O3 E ` t4 [4 p
6 rows in set
mysql> create temporary table temp as select min(id),name from student group by name;
$ Y) h& s, |' r$ GQuery OK, 3 rows affected7 m" _$ r; t" t' |2 a3 j
Records: 3 Duplicates: 0 Warnings: 0
mysql> truncate table student;! }1 W/ ~# ?* W) L9 |, D$ T
Query OK, 0 rows affected
mysql> insert into student select * from temp;1 F' \. C8 S- Z5 w& T7 d5 f! x
Query OK, 3 rows affected
# d& t7 u* I0 I0 b" m3 ZRecords: 3 Duplicates: 0 Warnings: 0
mysql> select * from student;3 l7 M; Y5 F7 ?
+----+------+7 H$ Z4 O1 }! q t4 n* d4 @1 I/ v
| ID | NAME |
$ }' }+ W2 b/ {* v+ ^7 l' _. I+----+------+% S6 }! g a" [+ G0 N( {
| 11 | aa |+ @0 o2 _( |) C9 u4 Y
| 13 | bb |; X7 g% u4 A: @1 V5 P$ d! I# z/ M& `
| 16 | cc |
3 G" J8 |: p) m+ s+ r9 S+----+------+! U; n) v( ^, z* ~
3 rows in set
mysql> drop temporary table temp;
( w4 `+ N# J. i8 E: pQuery OK, 0 rows affected9 {0 G0 N( M1 d, r
这个方法，显然存在效率问题。
方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：) l9 z# v Q" d" l
mysql> create temporary table temp as select min(id) as MINID from student group by name;
% y* n/ I& n, |Query OK, 3 rows affected
$ G$ H9 }; U9 d- W8 GRecords: 3 Duplicates: 0 Warnings: 0
mysql> delete from student where id not in (select minid from temp);' ~$ M$ u* S2 \& z3 ]2 Z9 P
Query OK, 3 rows affected
mysql> select * from student;
2 a# v& X. m9 h4 A5 O0 F4 R+----+------+
7 V4 V" K9 e' Y| ID | NAME |4 x* ^3 h- `& b) j
+----+------+
5 ~+ R# s0 L! E/ D7 e| 11 | aa |
) H2 N# P1 m* w* u| 13 | bb |
$ `; r* i/ W0 k" @5 _" F| 16 | cc |
7 L, Z4 n4 W1 t+ r+----+------+
5 c" s/ X) B" g5 c7 h( l3 rows in set
方法3：直接在原表上操作，容易想到的sql语句如下：
mysql> delete from student where id not in (select min(id) from student group by name);3 V/ C5 P$ N, {7 S0 R9 d6 |* b! F- G
执行报错：1093 - You can't specify target table 'student' for update in FROM clause, ]( W4 w! K# w) `
原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
/ W- v x+ a$ l2 t9 r) V怎么规避这个问题？
9 b8 w" [/ g! f: |! V- `" _再加一层封装，如下：0 M7 }, V9 d# D
mysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
5 \9 l5 J1 E( U L: tQuery OK, 3 rows affected
mysql> select * from student;
& x, q' p7 T; n! u2 X+----+------+
0 ^/ t G' O, D5 x$ u| ID | NAME |9 a2 {& m2 _9 Q5 A7 D
+----+------+2 s& l% Y6 ]* Q1 D8 U: }9 e
| 11 | aa |
9 n$ E. C) L& ?, @" ?. F7 h| 13 | bb |
4 ^& s8 |) a) e# u o6 \| 16 | cc |
2 t% A. q' u) r' N) s+----+------+
: ]0 a. Y, {! D4 |; Z3 rows in set
! _' C7 d) i9 R" v
方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);! m/ {0 ?! G+ X3 D6 d- R
# o1 ~. s/ ^; p0 z1 `4 u/ F, V; _: `/ J: x, h0 W' d5 m1 s8 w" I( b, \. m- g
| 欢迎光临 中国网络渗透测试联盟 (https://www.cobjon.com/) |
Powered by Discuz! X3.2 |