找回密码
 立即注册
欢迎中测联盟老会员回家,1997年注册的域名
查看: 1553|回复: 0

mysql删除重复记录,保存Id最小的一条

[复制链接]
发表于 2016-8-23 20:58:17 | 显示全部楼层 |阅读模式
方法1:6 }2 G0 S4 ?, p9 f. k, k$ O
1、创建一个临时表,选取需要的数据。
9 n# \4 B+ ~% v6 f2、清空原表。
7 @: \; Q8 x* \3、临时表数据导入到原表。) D5 a( U3 y1 v7 t- ]' Y
4、删除临时表。
0 t* @% D! B( wmysql> select * from student;5 `  U$ W- b+ N7 }
+----+------+
3 I5 W% z$ n$ e7 p) [% C| ID | NAME |
7 j$ P9 Q6 c' Y1 f- w+----+------+
8 Q$ {" g( q( ^' C| 11 | aa |
0 @- e6 a0 D6 t9 b/ G* @+ _! e| 12 | aa |
% I: _# _9 \7 ]# u6 G| 13 | bb |$ _8 T" ^: U0 }+ L* g
| 14 | bb |4 c0 {, t0 f  {2 j+ b# z# o3 m
| 15 | bb |
: e+ r  H  F0 D' Y. S| 16 | cc |& z* q: ~- v" I9 F- A7 E* i  w
+----+------+
4 N5 K! F; Z. L& {. w6 rows in set
mysql> create temporary table temp as select min(id),name from student group by name;# x  \& C; Q; o8 H* x# I
Query OK, 3 rows affected' X5 c$ s: U8 C% ^9 v; x
Records: 3 Duplicates: 0 Warnings: 0
mysql> truncate table student;, Z1 R/ {7 V7 R) M  L, Q
Query OK, 0 rows affected
mysql> insert into student select * from temp;
/ F5 k4 M# T* v9 v5 `* D! c1 NQuery OK, 3 rows affected
- b# C; s, q4 B6 n6 aRecords: 3 Duplicates: 0 Warnings: 0
mysql> select * from student;. ]1 t8 F$ j! i2 i( o" J* \
+----+------+# U- a; }, o/ I# d
| ID | NAME |
8 ]. g! D8 u9 n$ b% {+----+------+
; {0 U$ ?5 B& n9 e. T| 11 | aa |) |1 j# m; s0 Z: t9 w  J6 s
| 13 | bb |6 h( c% x  j% _/ Q" J& {4 [, P
| 16 | cc |
7 M# p' F9 E0 Y+----+------+
; u: U* Q4 j7 A  ^5 }3 rows in set
mysql> drop temporary table temp;3 M6 ^/ h. w' u% I8 l: w9 c) d
Query OK, 0 rows affected9 n: u  g# E' h, T& r/ k9 B; i! w* `
这个方法,显然存在效率问题。

方法2:按name分组,把最小的id保存到临时表,删除id不在最小id集合的记录,如下:' ^- @+ b9 M5 q% M
mysql> create temporary table temp as select min(id) as MINID from student group by name;
, I0 o: D# i* `8 {Query OK, 3 rows affected
, x( J$ Q: c: w6 @! RRecords: 3 Duplicates: 0 Warnings: 0
mysql> delete from student where id not in (select minid from temp);+ ?0 w6 l. }5 X- b
Query OK, 3 rows affected
mysql> select * from student;
! X  ~9 l8 ?5 r+----+------+
1 e9 }( M6 {3 ?; `4 T6 b8 I| ID | NAME |* A- L% q& U& `' _! O7 Y7 ]
+----+------+
+ o$ W" R- M7 ^: m) c/ x& p| 11 | aa |3 \1 }4 {+ l. A3 x" v0 s: G- O# c
| 13 | bb |
( G; k- r0 ?3 r. b| 16 | cc |
; ]2 x4 K, X* R1 K' D7 C- d+----+------+) m: E# k9 A* L
3 rows in set

方法3:直接在原表上操作,容易想到的sql语句如下:
mysql> delete from student where id not in (select min(id) from student group by name);
( n# {% ^3 q0 W4 \: j/ K执行报错:1093 - You can't specify target table 'student' for update in FROM clause
6 q# W+ A& q4 I" Z+ s原因是:更新数据时使用了查询,而查询的数据又做了更新的条件,mysql不支持这种方式。4 t- A* y, `! ~8 @5 E
怎么规避这个问题?
) w1 r$ s; `" j* H! U再加一层封装,如下:
8 h: N3 M5 R( F' M$ V9 f! wmysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);+ \' }* F0 l; s6 v/ |! K( K7 H
Query OK, 3 rows affected
mysql> select * from student;) R) g1 @+ c- l' e! e
+----+------+- k. V, h9 [$ {  {0 P" h
| ID | NAME |: {" }8 ~; I9 E. a7 x' q
+----+------+: D- p; W) {' f) s8 M
| 11 | aa |; _% @- J9 M. g7 c* z0 N' K
| 13 | bb |8 J" K) C, U  S; T! f
| 16 | cc |! S5 ^8 [3 D6 t: ]
+----+------+
# i% D% ~5 T7 h& ~+ i3 rows in set

: t7 L- q4 G. e( G% u方法三例如:delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);1 @* t( X- k3 `' k: G

2 Y" v& b3 d1 o: C% m
! ~' x; Y" O$ k
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

快速回复 返回顶部 返回列表