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

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

\#GW6K"_b[/S4W0SQL>descemployee51Testing软件测试网C0l
U,{!z/_5T0wf

51Testing软件测试网KN)@2\y8k,h

$qGmtUH/Pj)I:V0

 Name                                      Null?    Type
-@iT2_(e;L0 ----------------------------------------- -------- ------------------

f@_!VP8mEE\0

9a,}/v;e/f7N0

emp_id                                                NUMBER(10)
N1j:N!t| f0SK'O0emp_name                                           VARCHAR2(20)

.Q%e7\.d%{#t4l0

salary                                                 NUMBER(10,2)

Z5d\JIR7CU3I1O051Testing软件测试网{,Ju,J,Z)B

v7i:p M7AS0 

ITtO,MS0

可以通过下面的语句查询重复的记录：

3k3@~7` P0

,|6pec^+f,l
`S0

SQL>select*fromemployee;51Testing软件测试网/X4A GT"Ri

.JJ9QH;kG]051Testing软件测试网
Dl/XUu2X

51Testing软件测试网MBR.T(TW)`

   EMP_ID EMP_NAME                                 SALARY

7z I*~:r{9H Y051Testing软件测试网_2m Zp'Q

[/M#bnP&I1|U051Testing软件测试网9|}5fSj g

---------- ---------------------------------------- ----------51Testing软件测试网1@vCu z`Y[6X

n8T"[8j/u~0

        1 sunshine                                     10000

!t*T,ey d8W'D0

{\E5Vmw D8A+i051Testing软件测试网0V9]v~^'x

        1 sunshine                                     1000051Testing软件测试网"@,b$pQ+D$HvFR'W i8t

51Testing软件测试网8Xf0iPZsU TN]

        2 semon                                        2000051Testing软件测试网;vf1C&A$IY

51Testing软件测试网X3W J]*o/qx3v

2gsdA"Vc@ h0MV051Testing软件测试网:I"\%O9ZRf!r6K~"g

        2 semon                                        20000

,q~4P.Ps0

51Testing软件测试网 Kg(ijP*W3mb.z

        3 xyz                                          30000

xv ZNP$Cr"Gc#c051Testing软件测试网!rGr+Zl2Z

jz#u6i Ac1p~O0

        2 semon                                        2000051Testing软件测试网E,[;j(mB7~4G%rw

!o4?z$lG+e0

U2EQ K8I"KPo0

^NjFL0i$s0
?&V_5D8k:b2e0SQL>selectdistinct*fromemployee;51Testing软件测试网
U@yaD7v2y"c
D

EyQ\F]UMN1x0

   EMP_ID EMP_NAME                                    SALARY51Testing软件测试网'O0vA;A#_3JV

51Testing软件测试网eR-a I#O

---------- ---------------------------------------- ----------51Testing软件测试网v{/S\6Q_zOp

        1 sunshine                                     1000051Testing软件测试网3C"r{B0Lo%_5h#g&K'D [

2d9V%Dv%Kj5zl051Testing软件测试网~QU8D
QbU_

        2 semon                                        2000051Testing软件测试网;a!gwp2d#R h

51Testing软件测试网!R~O8\:B&X

        3 xyz                                            3000051Testing软件测试网pf;D2e @

g]p G/U({f051Testing软件测试网g:iz.we nf$i

SQL> select*fromemployeegroupbyemp_id,emp_name,salaryhavingcount(*)>151Testing软件测试网-or8nU-qK5h D

   EMP_ID EMP_NAME                                    SALARY

}:C6~;k3n1Zv1W!j0

---------- ---------------------------------------- ----------51Testing软件测试网;muMp+j"j2L

51Testing软件测试网:oR@"cjG9B}8_(A

        1 sunshine                                     10000

e!\;iI[,G Z051Testing软件测试网`u"^RXJ`T

        2 semon                                         20000

f%va8{([Q0

51Testing软件测试网b&kqm]r?2i8V_{4p

51Testing软件测试网LH:dI^_E
SQL>select*fromemployee e1

E#l4a(~gr0

whererowidin(selectmax(rowid)fromemploye e2
|9QA(ed Pt5? T} X0 wheree1.emp_id=e2.emp_idand

5]Djv&N6S051Testing软件测试网6_1C#Q^p

 e1.emp_name=e2.emp_nameande1.salary=e2.salary);51Testing软件测试网q&PJRdI6Zg@
]

51Testing软件测试网4F,jtW'ba

51Testing软件测试网k6V$YN$@

\5G'[_`Bw:H0 

MLO.d%O{};^s8Ec051Testing软件测试网1yE+c0d;UR

   EMP_ID EMP_NAME                                    SALARY51Testing软件测试网\$y0K{J

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

h[nQK sJ:j1YH051Testing软件测试网6z.~`Bs#n7O#V

~I5IcrL051Testing软件测试网1WY;|0TC5te2F"U

        1 sunshine                                     10000

n}R@8~:L6Ug+P0

51Testing软件测试网5?-\n9RS^

        3 xyz                                           3000051Testing软件测试网` Oz_[0Mm's

        2 semon                                        20000

z/^.eZ:}W`\0

2.删除的几种方法：51Testing软件测试网^3OtCk`8mQ3F

51Testing软件测试网5N HDq0]Q&Uu)E,G

（1）通过建立临时表来实现51Testing软件测试网Hf.f2Ygz

7\.v!^M#z5t*\-Wz
V$S0

F/m#e q {Gw+[Z0

SQL>create table temp_emp as (select distinct * from employee) 51Testing软件测试网9K0z&E8pR5`,y+i
S

51Testing软件测试网/Q+eZ!y \

51Testing软件测试网-]9X!k}$}+Az'A

SQL>truncatetableemployee;(清空employee表的数据）51Testing软件测试网kl*n[} Q

51Testing软件测试网S-DT/] | ?"X9nT

SQL>insertintoemployeeselect*fromtemp_emp; (再将临时表里的内容插回来）

M){#p} G%]0

Q*T-Xm i0

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

4gc;he6wz:x#~0

SQL>deletefromemployee e2whererowidnotin(
aVx@7K6J7~b@0       selectmax(e1.rowid)fromemployee e1 where

[7d,Y7]h}/v0

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

;V"PE/S?"A3v0

51Testing软件测试网l]z#x1q0u _+B

Z(t^0KC3J1v2nA!l^0SQL>deletefromemployee e2whererowid<(
r`2h,bm"~0       selectmax(e1.rowid)fromemployee e1where
d8hBTGf7B0       e1.emp_id=e2.emp_idande1.emp_name=e2.emp_nameand

v4L'mWu-[051Testing软件测试网 t8m
C.~Pu?b\(C]

                 e1.salary=e2.salary);51Testing软件测试网7l&hf;j$o_

51Testing软件测试网$T,W2`-Xs"_}~

'Ut(L P&G051Testing软件测试网+W XuW7^4E [#`5W

6Z@#HC1]RKJh0（3）也是通过rowid，但效率更高。

SQL>deletefromemployeewhererowidnotin(
5^"a"b'J@
j9B0       selectmax(t1.rowid)fromemployee t1groupby51Testing软件测试网b0KJqu Wlr*f

51Testing软件测试网0z G-h zv(L9u|.R

)a8dEx&x*sv9Ep0

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

U_\(D}0XG0

51Testing软件测试网vIP2{C,n

51Testing软件测试网g.|g+OChZ F0O$K

51Testing软件测试网aS6CL5mu"Y+h9R

R'{s$T0N
s^0   EMP_ID EMP_NAME                                    SALARY

51Testing软件测试网rTs$N2u

)g#W3Xy7zIz051Testing软件测试网x5x E{
^9h1R

---------- ---------------------------------------- ----------51Testing软件测试网 \-l*vYDY

#B&V1se(T0

        1 sunshine                                     10000

a6mj9w3Rw1OR0

51Testing软件测试网+Wu1?"o
N(["D0N

        3 xyz                                           30000

:A`(\ZEr(k051Testing软件测试网;o eD;]D2v_0J

1@$F-]%Xa]051Testing软件测试网XCvoW4N@-W

        2 semon                                        2000051Testing软件测试网%mpm4oM&}hg/W4@

51Testing软件测试网v
g1AAmA-x

51Testing软件测试网~"Z @"W"D;SrF

 51Testing软件测试网)[KjZ
qDw;e
gE+k

$q!y~4Uyl+g}051Testing软件测试网*W HjAV7_

"h.e Sp%}|K051Testing软件测试网:cF2w0pnx

51Testing软件测试网)S*N%?tc&n ~{p

SQL>descemployee51Testing软件测试网Z:ZH~*ta%t

51Testing软件测试网 W:Fw+{F A.r

'{Wj'x+S;|@0

 Name                                      Null?    Type51Testing软件测试网 x_'x\X8k
 ----------------------------------------- -------- ------------------

5JI&H#zn-|@K0

.}/|]gk7O;rO4xZI H0

emp_id                                                NUMBER(10)
w$j,N)U e3uu Bj0emp_name                                           VARCHAR2(20)51Testing软件测试网J/b D`F7CP.c3d

51Testing软件测试网C/BK8@N-nO

salary                                                 NUMBER(10,2)51Testing软件测试网s]FD-W|Q;T7N