create table test_0210(id number,name varchar2(32),age number);
ITPUB个人空间M Tx7y B p)r#kinsert 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 xvRL f S2?ITPUB个人空间.c-O)Z*t]select * from test_0210;
ITPUB个人空间3s Y8k,fb2c6K2w \,wSQL> select * from test_0210;
ITPUB个人空间2O(F9D7i4WE#OJ hU ^&Y)TCXw{11269677ID NAME AGE
&vnq:A,Y11269677---------- -------------------------------- ----------
ITPUB个人空间x)ns3a1[L1 abc 32
ITPUB个人空间 pc!c x7f \#F2 def 33
yrZ1|bmT112696773 def 45
ITPUB个人空间1N*NnM h!X5Z|D(F#@I11269677第一种写法
sql:
:V b[ q%zL5f11269677SQL> select a.*
&c"m ~1Z q [F-F112696772 from test_0210 a,test_0210 b
ITPUB个人空间.L G"B _Yo0g+AY3 where a.id <> b.id and a.name = b.name ;
r v5qG ? SuN11269677ITPUB个人空间,a5A:~#lu]8s0o#j!rID NAME AGE
W CuSN$qki[H Z11269677---------- -------------------------------- ----------
)Jl!Z"a#Mc112696773 def 45
ITPUB个人空间 {tf)S%Q {`I w(E2 def 33
1Y B'Zz*R^5Mm11269677ITPUB个人空间I-P&CB6q ut第二种写法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,O9j I11269677grW8uA;ofEw11269677ID NAME AGE
ITPUB个人空间T0B M7nT oQ'q---------- -------------------------------- ----------
$_)Q ~B4[ D112696772 def 33
c$^.NI#U+T+Z112696773 def 45
$B6?3t.T bE{11269677v"F6N \za O'A11269677第三种写法sql 利用分析函数
ITPUB个人空间r%oz9i2^uSQL> select id,name,age
#h(oe U2F4q2G5r112696772 from (select id,name,count(name) over(partition by name) as rn,age
ITPUB个人空间 KtlY%nD:i3 from test_0210)
ITPUB个人空间@\w2e8rR_4 where rn > 1
ITPUB个人空间'ku0Q1@M\,~!Q }#E3p0r5 ;
\-q d Iy?{11269677;E?S(?D11269677ID NAME AGE
*~T@/O gv(B3ivw11269677---------- -------------------------------- ----------
ITPUB个人空间v`owi2 def 33
ITPUB个人空间#u3lSU-Y#Q_ u hjq3 def 45
ITPUB个人空间O9[ x$D j ]8ROA[&F{ G;i[\6KT11269677SQL>
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/11269677/viewspace-613568/
，如需转载，请注明出处，否则将追究法律责任。
