ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 用sql语句找出表中有重复记录的三种sql写法

用sql语句找出表中有重复记录的三种sql写法

原创 Linux操作系统 作者:kvsion 时间:2009-08-30 12:35:19 0 删除 编辑
create table test_0210(id number,name varchar2(32),age number);ITPUB个人空间MTx7y B p)r#k
insert into test_0210 values(1,'abc',32);
'^sC w/y5R Y W&y[11269677insert into test_0210 values(2,'def',33);
$A}0eP9yD3@x11269677insert into test_0210 values(3,'def',45);
d(MT;W`Zc(Q\11269677commit;ITPUB个人空间@,g xvRLfS2?
ITPUB个人空间.c-O)Z*t]
select * from test_0210;ITPUB个人空间3sY8k,fb2c6K2w \,w
SQL> select * from test_0210;ITPUB个人空间2O(F9D7i4W

E#OJhU ^&Y)TCXw{11269677ID NAME AGE
&vnq:A,Y11269677---------- -------------------------------- ----------ITPUB个人空间x)ns3a1[L
1 abc 32ITPUB个人空间 pc!c x7f\#F
2 def 33
yrZ1|bmT112696773 def 45ITPUB个人空间1N*NnMh

!X5Z|D(F#@I11269677第一种写法sql:
:V b[q%zL5f11269677SQL> select a.*
&c"m~1Z q [F-F112696772 from test_0210 a,test_0210 bITPUB个人空间.LG"B_Yo0g+AY
3 where a.id <> b.id and a.name = b.name ;
rv5qG ? SuN11269677ITPUB个人空间,a5A:~#lu]8s0o#j!r
ID NAME AGE
W CuSN$qki[HZ11269677---------- -------------------------------- ----------
)Jl!Z"a#Mc112696773 def 45ITPUB个人空间 {tf)S%Q {`I w(E
2 def 33
1Y B'Zz*R^5Mm11269677ITPUB个人空间I-P&CB6qut
第二种写法sql:
,Qr2Ik8M X11269677SQL> select a.* from test_0210 a,(select name,count(*) from test_0210 b group by name having count(*)>1) b
s^.Kt*`$y"|5s!i9QkE112696772 where a.name=b.name;
UX,O9jI11269677
grW8uA;ofEw11269677ID NAME AGEITPUB个人空间T0BM7nToQ'q
---------- -------------------------------- ----------
$_)Q~B4[ D112696772 def 33
c$^.NI#U+T+Z112696773 def 45
$B6?3t.T bE{11269677
v"F6N \za O'A11269677第三种写法sql 利用分析函数ITPUB个人空间r%oz9i2^u
SQL> select id,name,age
#h(oeU2F4q2G5r112696772 from (select id,name,count(name) over(partition by name) as rn,ageITPUB个人空间KtlY%nD:i
3 from test_0210)ITPUB个人空间@\w2e8rR_
4 where rn > 1ITPUB个人空间'ku0Q1@M\,~!Q }#E3p0r
5 ;
\-qdIy?{11269677
;E?S(?D11269677ID NAME AGE
*~T@/O gv(B3ivw11269677---------- -------------------------------- ----------ITPUB个人空间v`owi
2 def 33ITPUB个人空间#u3lSU-Y#Q_uhjq
3 def 45ITPUB个人空间O9[x$D j ]8ROA[

&F{ G;i[\6KT11269677SQL>

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11269677/viewspace-613568/,如需转载,请注明出处,否则将追究法律责任。

上一篇: ROLLUP和CUBE
请登录后发表评论 登录
全部评论

注册时间:2008-09-20

  • 博文量
    17
  • 访问量
    23022