中国网络渗透测试联盟
标题: mysql删除重复记录，保存Id最小的一条 [打印本页]
作者: admin 时间: 2016-8-23 20:58
标题: mysql删除重复记录，保存Id最小的一条
方法1：: q: s) \* `3 J) }0 I1 g s/ f' {
1、创建一个临时表，选取需要的数据。
X' s' r+ C7 T$ h* Z3 |) J2、清空原表。" U: G. l* ?/ o
3、临时表数据导入到原表。
- X# F3 A8 E4 K+ y% C* ~6 e% W) r4、删除临时表。
' N% D; V+ t1 S$ E' o* d9 Z9 ?mysql> select * from student;+ n" V1 n) g8 ^* V4 e9 Z0 T
+----+------+3 B5 H% @. w s. p, a. S0 L
| ID | NAME |: l+ G8 h. B. n2 j; J) r0 Q
+----+------+
8 _( P/ ^' U; W c; E% E0 x| 11 | aa |
+ X4 Z; ?' k. D! v| 12 | aa |1 b$ C4 L% P9 _5 S" B
| 13 | bb |% `# |; X4 A9 k* l' j
| 14 | bb |9 S. f- W2 M7 H& s5 e$ n
| 15 | bb |/ M `/ W1 b5 H8 f* r! ?; e0 n) q
| 16 | cc |
: x6 [4 D8 K) R( R ^1 ]3 }+----+------+
& Y4 A- i& m# ]. I. O' `7 {6 rows in set
mysql> create temporary table temp as select min(id),name from student group by name;
) ~4 |8 n) }0 X4 O$ N3 ?5 ^Query OK, 3 rows affected# C! k- S" M: f9 [9 G9 F6 }) h1 T
Records: 3 Duplicates: 0 Warnings: 0
mysql> truncate table student;" h% T, x' I# I0 V1 N% m& a
Query OK, 0 rows affected
mysql> insert into student select * from temp;
% V; }# P- R; ^+ X# |- D( a; TQuery OK, 3 rows affected
" l s Y& f) l+ A1 F9 i% GRecords: 3 Duplicates: 0 Warnings: 0
mysql> select * from student;
7 l6 I ~2 e( K/ Q+----+------+
* i) Z8 l" R$ `6 s0 r4 s1 U| ID | NAME |0 \& a$ V g" k O* j6 [( C
+----+------+: N$ S( q ?: P; Y$ h, X' P3 D
| 11 | aa |
$ k9 D8 W1 d- O3 U/ Z| 13 | bb |
, \; B* p, }8 a| 16 | cc |
0 S- f( @7 K9 J* K+----+------+
& ]) l7 h: A/ t T* S3 rows in set
mysql> drop temporary table temp;+ s+ B0 u2 g/ D
Query OK, 0 rows affected
5 O- ^- o4 S" o$ `' P A$ M6 q这个方法，显然存在效率问题。
方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
8 n* s5 k7 N# h9 \3 o6 ^mysql> create temporary table temp as select min(id) as MINID from student group by name;
9 f! [: q% {& n9 w9 `; l2 I: VQuery OK, 3 rows affected- E; Q( I }2 K' B3 V, _. }
Records: 3 Duplicates: 0 Warnings: 0
mysql> delete from student where id not in (select minid from temp);
! L/ O8 ?; @- W' y! R( EQuery OK, 3 rows affected
mysql> select * from student;
5 @% K" |7 q9 _1 T c+ H3 @+----+------+& F9 C, i( }; M! e) t
| ID | NAME |4 u- ?; R E; Y+ ?/ j& N, r$ y
+----+------+5 _9 o6 x) R7 D! w8 q9 r; |
| 11 | aa |
5 J5 A$ D$ ^* s9 _| 13 | bb |
( Y+ H l8 P% w; u, E# F| 16 | cc |7 r6 S. D5 |/ R
+----+------+, W2 d& u) K4 j7 u$ W: _
3 rows in set
方法3：直接在原表上操作，容易想到的sql语句如下：
mysql> delete from student where id not in (select min(id) from student group by name);
' c4 \- M$ M# E% r执行报错：1093 - You can't specify target table 'student' for update in FROM clause
( H% {* J. K& f7 T+ M$ _0 L原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
0 ^+ `$ x, Z" O `5 g' f怎么规避这个问题？9 D" r# Y& I1 `0 {6 A
再加一层封装，如下：
/ W8 ^+ U% p' o( jmysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b); Y; p8 b* r* V1 C! r
Query OK, 3 rows affected
mysql> select * from student;" n% w3 i, i1 C, u
+----+------+1 z3 x$ O2 ?: e+ N& Q8 w
| ID | NAME |
5 L+ n" j- U% Z. g8 {0 p+----+------+
! _% q1 e' z$ t+ i# U; |- o; I| 11 | aa |
. I |9 M, i/ z; Y& P$ p7 p9 e& O" F| 13 | bb |* b' N& Q0 n9 E% O$ J1 v
| 16 | cc |
' f- ]8 e; n$ k, q) T8 N. W* l4 D+----+------+
[+ B1 e: M) M* [, }3 rows in set
4 w9 Y! g. D, ^: \& ^, S
方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);# E$ y- V' O F3 M8 E; A
; o3 k+ Z1 j+ I. M# y8 ^
; Z0 l- i/ Y+ ~! @
| 欢迎光临 中国网络渗透测试联盟 (https://cobjon.com/) |
Powered by Discuz! X3.2 |