找回密码
 立即注册
查看: 2241|回复: 0
打印 上一主题 下一主题

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

[复制链接]
跳转到指定楼层
楼主
发表于 2016-8-23 20:58:17 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
方法1:
; x! r2 N6 O; H3 I0 T; f8 o9 `1、创建一个临时表,选取需要的数据。
4 o) m6 a& c8 i7 y- M# F2、清空原表。
2 R8 V' i- g& c4 {$ _3、临时表数据导入到原表。
6 T% f' z# }$ z$ _" e4、删除临时表。
- s$ O& k2 I* H* j* b0 pmysql> select * from student;2 V0 f1 U- S0 |. v- w& K
+----+------+
" T$ v7 F2 ~+ Y- j2 P" n7 N* A| ID | NAME |
" B  w& d4 E: |6 Y* B+----+------+" r( l2 K: ~* A, _! n0 }
| 11 | aa |
$ u- o, [+ p2 I3 u| 12 | aa |1 _, w0 W* j1 x3 {; ?$ s/ C
| 13 | bb |3 O1 r) U& Z9 V
| 14 | bb |
' {+ H! s: l4 Q# I2 g: E| 15 | bb |
: w  @+ N5 D/ J$ F* M$ m| 16 | cc |9 K% W+ W3 H- o2 O: ~( F9 \0 K
+----+------+: ?; U; E! R7 a9 [6 ?
6 rows in set
mysql> create temporary table temp as select min(id),name from student group by name;
% ?: G9 {7 O4 s+ F4 `Query OK, 3 rows affected. T# w" O' @# M: {( g
Records: 3 Duplicates: 0 Warnings: 0
mysql> truncate table student;$ ^- Y) R1 j- g+ O
Query OK, 0 rows affected
mysql> insert into student select * from temp;6 U) Y9 e' {% S+ R' F. j) ]
Query OK, 3 rows affected
: R) Y( V$ y. ^: w1 z0 kRecords: 3 Duplicates: 0 Warnings: 0
mysql> select * from student;
" i' H! I- q( c* J2 `+----+------+$ a, S# W) d- c) ]
| ID | NAME |
& N5 D5 J5 ^+ @0 W+----+------+
$ W3 F' |9 C: v4 z+ e( D| 11 | aa |" b0 Y) Y2 A/ W
| 13 | bb |0 `4 u3 A: n. y- `
| 16 | cc |  p5 @7 t$ P# `
+----+------+6 ^& I( {8 r6 p, Q  D$ t2 ~3 w
3 rows in set
mysql> drop temporary table temp;
$ p- U" U* t9 i- a- u( f% I* y) OQuery OK, 0 rows affected
* R4 e# u; X/ u这个方法,显然存在效率问题。

方法2:按name分组,把最小的id保存到临时表,删除id不在最小id集合的记录,如下:% @# ]& K, J+ B4 q6 P# G3 _7 w* s4 l
mysql> create temporary table temp as select min(id) as MINID from student group by name;: t: x; H5 Y- {' e/ U) X' Q
Query OK, 3 rows affected
: @5 i2 |! A+ aRecords: 3 Duplicates: 0 Warnings: 0
mysql> delete from student where id not in (select minid from temp);
& k% e" s8 s' c6 f5 pQuery OK, 3 rows affected
mysql> select * from student;+ ?. r' d3 Q# c8 E2 j& _! V2 S8 B
+----+------+
5 X$ z* `. d9 V3 M& h1 Y| ID | NAME |
6 o: C  w" l% E+----+------+6 @) X5 {3 `& }2 ~/ b) G  w9 t
| 11 | aa |. h" y5 ?3 Q0 @! V% v  }
| 13 | bb |* S* I8 X; x! Z0 w5 [$ |4 D$ X
| 16 | cc |$ S+ y6 f, |4 Z
+----+------+$ M  C8 b; R6 @/ k1 w
3 rows in set

方法3:直接在原表上操作,容易想到的sql语句如下:
mysql> delete from student where id not in (select min(id) from student group by name);
# @' d& F8 ~' _6 p' ~0 J$ t/ g执行报错:1093 - You can't specify target table 'student' for update in FROM clause2 g9 |3 F5 W+ F
原因是:更新数据时使用了查询,而查询的数据又做了更新的条件,mysql不支持这种方式。$ X6 }! \  G( J- n! \
怎么规避这个问题?. E( L$ f8 |1 v8 W9 S. U
再加一层封装,如下:
, g% Q: ^" c! i( G. Zmysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
. b3 L1 j+ m3 z4 k! ?% `8 \: CQuery OK, 3 rows affected
mysql> select * from student;
% o; R. D) _$ Y: b" \/ P( P  ~" m; o+----+------+2 P1 \$ l. Q1 ]6 B  j
| ID | NAME |. z5 t. K5 ]0 N$ G8 o# b/ f) |4 d
+----+------+( I. L3 V" M" G6 @* }. i
| 11 | aa |/ G4 z% p. \+ ?3 D& ?
| 13 | bb |7 u. o) L# n6 d. Z
| 16 | cc |, J. S7 _0 [2 q2 W' h" O! D3 i
+----+------+
0 P) L' o" v! ^. D3 rows in set
( @4 U. m, j5 [$ d. l# ]
方法三例如:delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);
# t% o8 ~8 S: ?+ R7 U7 d
# R1 W" ~) j& p& v6 T4 ~- \
' K0 p* h1 o$ b
回复

使用道具 举报

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

本版积分规则

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