例如一个表student(id int, name varchar2(20));
有数据：
ID NAME
———- —–
1 tom
2 jack
2 jack
3 mike
4 jack

以下语句可输出记录重复的行：
select distinct a.* from student a, student b where a.rowid<>b.rowid and a.id=b.id and a.name=b.name;
结果为：
ID NAME
——— ——————–
2 jack

以下语句输出名字重复的：
select distinct a.* from student a, student b where a.rowid<>b.rowid and a.name=b.name;
结果为：
ID NAME
——— —-
2 jack
4 jack

以下语句可删除重复的记录，并保留一条：
delete from student a where a.rowid<>(select max(rowid) from student b where a.id=b.id and a.name=b.id);

以下语句获得所有id重复的记录:
select id from student group by id having count(id)>1;

由于用了group by id，因此前面只能为select id，不能为select *；

但可用：
select id,name from student group by id,name having count(id)>1;
但having count(id,name)则不行。

但可用：
select id,name from student group by id,name having count(*)>1;

本文地址：http://www.caihonger.com/tech22/