利用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.* from test_0210 a,test_0210 b 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 where a.name=b.name;
ID NAME AGE
---------- -------------------------------- ----------
2 def 33
3 def 45
第三种写法sql 利用分析函数
SQL> select id,name,age from (select id,name,count(name) over(partition by name) as rn,age from test_0210) where rn > 1;
ID NAME AGE
---------- -------------------------------- ----------
2 def 33
3 def 45
SQL>