在SQL中删除重复记录(多种方法)

-F.R4k)OIh051Testing软件测试网Ace"fn"d

学习sql有一段时间了，发现在我建了一个用来测试的表（没有建索引）中出现了许多的重复记录。后来总结了一些删除重复记录的方法，在Oracle中，可以通过唯一rowid实现删除重复记录；还可以建临时表来实现...这个只提到其中的几种简单实用的方法，希望可以和大家分享（以表employee为例）。

9^5b&c}6wT051Testing软件测试网W}Le:d kl

SQL>descemployee

|Xm x GX4@H051Testing软件测试网 v} O0rO3~

51Testing软件测试网~(^%k*U%A#x(b

a2k0knJ0 Name                                      Null?    Type
V;g.g$q5LB LOK eUm0 ----------------------------------------- -------- ------------------

d @R%J/j1h051Testing软件测试网7dCd3W$O]

8lP E5xu3E051Testing软件测试网'h!biw'?,P2a

emp_id                                                NUMBER(10)
)Z+d5XfNJY0emp_name                                           VARCHAR2(20)51Testing软件测试网$O'_0o6L(uKZ

51Testing软件测试网A2U-h
e+CF

B)O|7z(@7g051Testing软件测试网[X^(w E4gF I

salary                                                 NUMBER(10,2)

M?*OTs
H
TT051Testing软件测试网;Ei{I2m3n

,\hh'i0q)g
GW0

3p#~#p&q1F4Ar!x4m1O$R0 

可以通过下面的语句查询重复的记录：51Testing软件测试网 clkl-] oK2^

51Testing软件测试网KY(M4nR:{3[

o9n&p/{3Kf0

SQL>select*fromemployee;51Testing软件测试网,J KP3Y*o Y]

51Testing软件测试网
rdYo
hI S$W

L5x \k/O'd051Testing软件测试网~*rPI ^:I{
j

51Testing软件测试网7Hayh4[$k*{0w

   EMP_ID EMP_NAME                                 SALARY51Testing软件测试网B.M]?N@`"t%{

&Dh(fO(m0

51Testing软件测试网 k(g(_8?+rC%tO

---------- ---------------------------------------- ----------

Z{-HM
@*hK8e0

51Testing软件测试网k#M _%c8g8_9Cug%zu

        1 sunshine                                     10000

%~nPP:\!n^0

t$nm(P!V'j/SBz4q051Testing软件测试网 ~ HF:gdxH7qi

        1 sunshine                                     1000051Testing软件测试网 G's"G&L"X

51Testing软件测试网C(b^3r)bKx

        2 semon                                        20000

7k-H7k5Us_0

.K[ |G d)? ^hXBp051Testing软件测试网+qm+K#F2qGu

        2 semon                                        2000051Testing软件测试网q0NXn \C\ sH,p

51Testing软件测试网 u*zw
v@(nOk+IW

        3 xyz                                          30000

xI6ro4k(Q051Testing软件测试网aP'I7e3sX7lVf3vi,i

51Testing软件测试网6R(O(j9iW|PC

        2 semon                                        20000

` Rx.@!CS1zJ9},nD051Testing软件测试网"i'|(D0XL'VI

#ddJ$j5mIX d0

h(N%k)}G X0W'We }0

y^a#on `
c051Testing软件测试网0[V^'d#Nn
SQL>selectdistinct*fromemployee;51Testing软件测试网 GMU`0A|

P8il~}R*q
@0

51Testing软件测试网Xf(l^ ZUzqT

   EMP_ID EMP_NAME                                    SALARY

9wkn[,C7n#_x
Mx051Testing软件测试网(X [&caNV.rw6g(]

#aa4y
Zr0

---------- ---------------------------------------- ----------51Testing软件测试网s'bW9l$b*e O

51Testing软件测试网L z@ }bI

O!k9Y6p;s0_9x B051Testing软件测试网Z/Uf{;`

        1 sunshine                                     10000

1{S;N;d!VZ(Q BBk0A051Testing软件测试网0@ o
FjYm:Y0f.`

51Testing软件测试网@3T#OUEh

        2 semon                                        20000

k_V/UZ5blV051Testing软件测试网xd"{H8U#s T^Qn[

kJ+f5`5L]B(N0

        3 xyz                                            30000

*zV J5xV!J$H\k4U0

kb5VXG!oHq-V0

SQL> select*fromemployeegroupbyemp_id,emp_name,salaryhavingcount(*)>151Testing软件测试网{z:Ivs(z

   EMP_ID EMP_NAME                                    SALARY

(?[|4IP|051Testing软件测试网+[(eB.ki"w&Pt7`mK

---------- ---------------------------------------- ----------

&R8e4C5];sQD0

        1 sunshine                                     1000051Testing软件测试网5K}iDT!k[

51Testing软件测试网*F2I
D#{8?6qJ vW.V1l

} ~P5bG'J0

        2 semon                                         2000051Testing软件测试网%L6d`,SW,x9Wy

zG)bA f u!d(hN0

*ym;l+J#_0SQL>select*fromemployee e1

%~ OAh`/K
a0

51Testing软件测试网cLf8Se9O

whererowidin(selectmax(rowid)fromemploye e2
/q$qW#l&PBe;?0 wheree1.emp_id=e2.emp_idand

nC5S?] x y`{)u051Testing软件测试网Ej"xq:r5n

51Testing软件测试网z~"Pm0BC5Dh

 e1.emp_name=e2.emp_nameande1.salary=e2.salary);51Testing软件测试网ozjc @^{

51Testing软件测试网&W ]/Ed [&T1[

n5G!Z7["do q W!d0 51Testing软件测试网 xV`\;X+K e

   EMP_ID EMP_NAME                                    SALARY

v$l,C(It$Ok051Testing软件测试网CE/itc#^!UD

]Z*JX~[051Testing软件测试网nW+Tt8]'n:Fi)gn

---------- ---------------------------------------- ----------51Testing软件测试网.KU#YX0`+^)v6f
P

51Testing软件测试网+{?e ?!D

e\M
zP D7mi2G0

        1 sunshine                                     1000051Testing软件测试网fj!g)HOLgDG

51Testing软件测试网B
cTQ;p#V/k

9L}8p$Ec0~0

        3 xyz                                           3000051Testing软件测试网x)K,?z$aL

51Testing软件测试网NwS2M/~^ EF

j1svH|;O/`-V~eD051Testing软件测试网 yIR(Ii7O*H-F'Mm

        2 semon                                        20000

}UR|4UT ~-`8J5j0

2.删除的几种方法：51Testing软件测试网F_7|gN

51Testing软件测试网 u[;]Z%vbe;O

（1）通过建立临时表来实现

|nG/A8i;WQl%C0

SQL>create table temp_emp as (select distinct * from employee) 

_q9o0q(sp)Z051Testing软件测试网r-~aJ6r G@#gc

$\2fz7c R!Wp)c0

SQL>truncatetableemployee;(清空employee表的数据）

S"{"wzpIR)C051Testing软件测试网%Vr~_5c+y
XW

51Testing软件测试网m.y&Ic"U.w

SQL>insertintoemployeeselect*fromtemp_emp; (再将临时表里的内容插回来）51Testing软件测试网`R
Y5q5J I7u#^

51Testing软件测试网l*|Yx/L4I%V{Me

51Testing软件测试网_2m:k]lg

51Testing软件测试网]"W|9lG?-l"P

( 2）通过唯一rowid实现删除重复记录.在Oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的，rowid确定了每条记录是在Oracle中的哪一个数据文件、块、行上。在重复的记录中，可能所有列的内容都相同，但rowid不会相同，所以只要确定出重复记录中那些具有最大或最小rowid的就可以了，其余全部删除。51Testing软件测试网 U+UN1YYS-dK

,V8[5H1|)P,lf051Testing软件测试网oa7kpY%A,n

SQL>deletefromemployee e2whererowidnotin(51Testing软件测试网 OS N`L3Afj
       selectmax(e1.rowid)fromemployee e1 where

~*BDPU%zY0

51Testing软件测试网 gN1h"H+O8g?

       e1.emp_id=e2.emp_idande1.emp_name=e2.emp_nameande1.salary=e2.salary);--这里用min(rowid)也可以。

L4j$`6^U.m051Testing软件测试网 J;M f7T.t+[^

51Testing软件测试网d.XK&R-XZ!od

#N:nd4z'^I0SQL>deletefromemployee e2whererowid<(
Yh!\RI0       selectmax(e1.rowid)fromemployee e1where51Testing软件测试网
]L~8S(t;r7Nd
       e1.emp_id=e2.emp_idande1.emp_name=e2.emp_nameand51Testing软件测试网fh}/|N!|3]9]

;d8Yz&H|oTGIT[051Testing软件测试网%~V$W4? ro

9b9b/n5pX&VpT"S,F0

                 e1.salary=e2.salary);51Testing软件测试网+y&UT)h0d

51Testing软件测试网R!v7aJ wU#X

/rnev?yWda:e0

+h(O
V@EN_f0（3）也是通过rowid，但效率更高。

*n'\gr4q I051Testing软件测试网8ZZdjk{9h { JE1DGYR

SQL>deletefromemployeewhererowidnotin(
0lZ7_$Y s{e-tCB0       selectmax(t1.rowid)fromemployee t1groupby51Testing软件测试网+fE"?Dl^

51Testing软件测试网6as-E!i3U}

        t1.emp_id,t1.emp_name,t1.salary);--这里用min(rowid)也可以。

^$\!@1R'e051Testing软件测试网#ONZ2svSAK

51Testing软件测试网|)ZXq.Zh_e[

U I2Pb-_'n`1]'X}051Testing软件测试网V:r&t_,t;rp

51Testing软件测试网-OY7f nBjK2j

   EMP_ID EMP_NAME                                    SALARY51Testing软件测试网3X%rL8I[-H

6r%K&KF W+N&B051Testing软件测试网uI1_Z-bN/B^

51Testing软件测试网g5c1}:IC!y

---------- ---------------------------------------- ----------

4V7qc+f
fB N051Testing软件测试网7v-kuPCD

51Testing软件测试网*YH0{0Hc8_w

        1 sunshine                                     1000051Testing软件测试网Wxn(xu'MEM qma

        3 xyz                                           3000051Testing软件测试网)_?$[6A p6e$|&N
}@

51Testing软件测试网6Z8_coOCs

        2 semon                                        2000051Testing软件测试网4N%vp%{2nre1c

51Testing软件测试网l:k(A@F1V

%w.m1x.Ug-Bn0w7u7\0

51Testing软件测试网jc;[2A.K

Y@]%N3g?0

IX%oW6N^?051Testing软件测试网h4pb,_A;CFb5a

51Testing软件测试网K"I x/i$Qq

Ut@!p%Wp-_.L051Testing软件测试网+a2Ru$k0F)`6[

PNc/UJF"x$\CW0SQL>descemployee51Testing软件测试网E6H5uU$E
ap

51Testing软件测试网)U G,^6`&EO

51Testing软件测试网(m7Y i4~)~`DT+O

 Name                                      Null?    Type51Testing软件测试网%X7JLU
E7o
 ----------------------------------------- -------- ------------------51Testing软件测试网u?^0uiCb/Z

51Testing软件测试网C4Ty3QC x

emp_id                                                NUMBER(10)51Testing软件测试网*BZe]d_V
emp_name                                           VARCHAR2(20)51Testing软件测试网`vdf0o}

51Testing软件测试网}%~h2f:sjia

51Testing软件测试网o'IWxrvwe7P

salary                                                 NUMBER(10,2)

wL!w7a[I8IEG0