在oracle里面我怎样取两条或更多相同的记录100分

Question

select name from tbname group by name having count(*)>1;

Answer 1

select * from table t1 where where t1.rowed !=
(select max(rowed) from table t2 
where t1.id=t2.id and t1.name=t2.name)
或者
select count(*), t.col_a,t.col_b from table t
group by col_a,col_b
having count(*)>1
如果想删除重复记录，可以把第一个语句的select替换为delete

Answer 2

--创建测试表
create table henry_test(a int,b varchar(10));
insert into henry_test values (1,'a');
insert into henry_test values (1,'a');
insert into henry_test values (1,'b');
insert into henry_test values (2,'a');
insert into henry_test values (2,'b');
insert into henry_test values (2,'c');
insert into henry_test values (2,'c');
insert into henry_test values (2,'c');
commit;
SQL> select * from henry_test;                                      A B
--------------------------------------- ----------
                                      1 a
                                      1 a
                                      1 b
                                      2 a
                                      2 b
                                      2 c
                                      2 c
                                      2 c8 rows selected
SQL> select a,b,row_number() over (partition by a,b order by a,b) num from henry_test;                                      A B                 NUM
--------------------------------------- ---------- ----------
                                      1 a                   1
                                      1 a                   2
                                      1 b                   1
                                      2 a                   1
                                      2 b                   1
                                      2 c                   1
                                      2 c                   2
                                      2 c                   38 rows selected
SQL> select * from (select a,b,row_number() over (partition by a,b order by a,b) num from henry_test) tbl where tbl.num=1;                                      A B                 NUM
--------------------------------------- ---------- ----------
                                      1 a                   1
                                      1 b                   1
                                      2 a                   1
                                      2 b                   1
                                      2 c                   1
上面的方法就是取所有字段重复的记录的第一条如果只想根据1个字段来判断就修改partition by后的字段，比如
SQL> select a,b,row_number() over (partition by a order by a,b) num from henry_test;                                      A B                 NUM
--------------------------------------- ---------- ----------
                                      1 a                   1
                                      1 a                   2
                                      1 b                   3
                                      2 a                   1
                                      2 b                   2
                                      2 c                   3
                                      2 c                   4
                                      2 c                   58 rows selected
SQL> select * from (select a,b,row_number() over (partition by a order by a,b) num from henry_test) tbl where tbl.num=1;                                      A B                 NUM
--------------------------------------- ---------- ----------
                                      1 a                   1
                                      2 a                   1

Answer 3

如果是删除的话就在查询的时候把ROWID加上，根据ROWID来删除。SQL> select * from (select henry_test.rowid,a,b,row_number() over (partition by a,b order by a,b) num from henry_test) tbl where tbl.num=1;ROWID                                                    A B                 NUM
------------------ --------------------------------------- ---------- ----------
AAB3GrAC8AAApCKAAA                                       1 a                   1
AAB3GrAC8AAApCKAAC                                       1 b                   1
AAB3GrAC8AAApCKAAD                                       2 a                   1
AAB3GrAC8AAApCKAAE                                       2 b                   1
AAB3GrAC8AAApCKAAF                                       2 c                   1

Answer 4

删除重复记录delete from henry_test c 
where not exists(
      select * from 
      (select henry_test.rowid,a,b,
      row_number() over (partition by a,b order by a,b) num 
      from henry_test) d 
      where d.num=1 and c.rowid=d.rowid)