利用sql語句找出表中有重複記錄的三種sql寫法

create table test_0210(id number,name varchar2(32),age number);
insert into test_0210 values(1,'abc',32);
insert into test_0210 values(2,'def',33);
insert into test_0210 values(3,'def',45);
commit;

select * from test_0210;
SQL> select * from test_0210;

ID NAME AGE
---------- -------------------------------- ----------
1 abc 32
2 def 33
3 def 45

第一種寫法sql:
SQL> select a.*
2 from test_0210 a,test_0210 b
3 where a.id <> b.id and a.name = b.name ;

ID NAME AGE
---------- -------------------------------- ----------
3 def 45
2 def 33

第二種寫法sql:
SQL> select a.* from test_0210 a,(select name,count(*) from test_0210 b group by name having count(*)>1) b
2 where a.name=b.name;

ID NAME AGE
---------- -------------------------------- ----------
2 def 33
3 def 45

第三種寫法sql 利用分析函式
SQL> select id,name,age
2 from (select id,name,count(name) over(partition by name) as rn,age
3 from test_0210)
4 where rn > 1
5 ;

ID NAME AGE
---------- -------------------------------- ----------
2 def 33
3 def 45

SQL>