找出表中重复行并删除

找出表中重复行并删除它们：

环境：

select * from encom as select * from emp;

insert into encom select * from emp;

insert into encom select * from emp;

解决方法：

列出相同行的rowid:all_select

SCOTT>select rowid  from encom where empno in (select empno from encom group by empno having count(empno) >1);

列出相同行中较小的一个rowid:min_select

SCOTT>select min(rowid) from encom group by empno having count(empno)>1;  

有2行相同的情况下

SCOTT>delete from encom where rowid in ( min_select );

有多行相同

SCOTT>delete from encom where rowid in ( all_select ) and rowid not in ( min_select );

利用rowid删除重复行

SCOTT>select * from encom where rowid in (select a.rowid from encom a,encom b where a.rowid>b.rowid and a.ename=b.ename and a.empno=b.empno);

利用max或min

SCOTT>select * from encom a where rowid not in (select max(b.rowid) from encom b where a.ename=b.ename and a.empno=b.empno);

SCOTT>select * from encom a where rowid < (select max(b.rowid) from encom b where a.ename=b.ename and a.empno=b.empno);

SCOTT>select * from encom a where rowid > (select min(b.rowid) from encom b where a.ename=b.ename and a.empno=b.empno); 

提高效率

SCOTT>select * from encom where rowid not in (select max(rowid) from encom b group by b.empno,b.ename);

查出表中ename段存在重复的项：

重复列名

SCOTT>select ename,count(*) from encom group by ename having count(*)>1;

多个列重复

SCOTT>select ename,job,count(*) from encom group by ename,job having count(*)>1; 

查出重复行

SCOTT>select * from encom where empno in (select empno from encom group by empno having count(empno)>1);

列出所有行不包含重复行

SCOTT>select distinct * from encom;

函数

row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的). 

SCOTT>select * from encom where empno in (select empno from (select empno,deptno,row_number() over(partition by empno order by deptno ) row_flag from encom) where row_flag >1 );