删除数据表中的重复行

原表数据

1. select subscrbid, prcplnid,min(begtime),min(endtime),count(*) from NEW_TRAIN_TAB_SUBSCRBPRCPLN group by subscrbid,prcplnid;　

　　　　

一、使用distinct删除表中的重复行：

1. create table table1_distinct as select distinct* from new_TRAIN_TAB_SUBSCRBPRCPLN;
2. truncate table new_TRAIN_TAB_SUBSCRBPRCPLN;
3. insert into new_TRAIN_TAB_SUBSCRBPRCPLN select * from table1_distinct;
4. select * from new_TRAIN_TAB_SUBSCRBPRCPLN;

　　　　

 二、利用oracle中表的属性rowid删除重复行

检索数据表中的重复行

1. select * from new_TRAIN_TAB_SUBSCRBPRCPLN a where rowid!=(select max(rowid) from new_TRAIN_TAB_SUBSCRBPRCPLN b where
2. a.subscrbid=b.subscrbid and a.prcplnid=b.prcplnid and a.begtime=b.begtime and a.endtime=b.endtime and
3. a.areaid=b.areaid);

删除重复行

1. delete from new_TRAIN_TAB_SUBSCRBPRCPLN a where rowid!=(select max(rowid) from new_TRAIN_TAB_SUBSCRBPRCPLN b where
2. a.subscrbid=b.subscrbid and a.prcplnid=b.prcplnid and a.begtime=b.begtime and a.endtime=b.endtime and
3. a.areaid=b.areaid);
4. select * from new_TRAIN_TAB_SUBSCRBPRCPLN;

删除成功。

 

 三、使用Group By删除重复行

　查找表中的重复数据：

1. 　　select subscrbid, prcplnid,min(begtime),min(endtime),count(*) from TRAIN_TAB_SUBSCRBPRCPLN group by subscrbid,prcplnid having count(*)>1;

　删除重复行：

1. delete from new_TRAIN_TAB_SUBSCRBPRCPLN group by subscrbid,prcplnid having count(*)>1;