Oracle--重复记录

 开发者博客：www.developsearch.com

 

 

查出重复记录

SQL》 select rowid，bm，mc from a where a.rowid！=（select max（rowid） from a b where a.bm=b.bm and a.mc=b.mc）;

ROWID BM MC

------------------ ---- --------------------

000000D5.0000.0002 1111 1111

000000D5.0001.0002 1112 1111

000000D5.0002.0002 1113 1111

000000D5.0003.0002 1114 1111

 

 

删除重复记录

SQL》 delete from a a where a.rowid！=（select max（rowid） from a b where a.bm=b.bm and a.mc=b.mc）;

删除4个记录。

SQL》 select rowid，bm，mc from a;

ROWID BM MC

------------------ ---- --------------------

000000D5.0004.0002 1111 1111

000000D5.0005.0002 1112 1111

000000D5.0006.0002 1113 1111

000000D5.0007.0002 1114 1111

 

 

根据单个属性字段判断重复性：

select * from tableName

 where columnName in (select columnName  from tableName

       group by columnName having count(columnName) > 1);

 

 

根据多个属性字段判断重复性:

select *  from tableName a

 where (a.columnName1, a.columnName2) in (select columnName1, columnName2

    from tableName  group by columnName1, columnName2 having count(*) > 1);

 

 

所有属性完全相同的情况：

select distinct * from td_tyorg;

 

 

删除某个字段相同记录(只留下rowid最小的记录，其中rowid为oracle系统自建的字段)：

delete from people

 where peopleId in (select peopleId

        from people group by peopleId having count(peopleId) > 1)

   and rowid not in (select min(rowid)

       from people group by peopleId having count(peopleId) > 1);

 

 

删除表中多余的重复记录（多个字段），只留有rowid最小的记录:

delete from vitae a

 where (a.peopleId, a.seq) in (select peopleId, seq

      from vitae group by peopleId, seq having count(*) > 1)

   and rowid not in (select min(rowid)

      from vitae group by peopleId, seq having count(*) > 1);

 

 

 如果该表需要删除重复的记录（重复记录保留1条），可以按以下方法删除

select distinct *

  into #Tmp

  from tableName drop table tableName

        select * into tableName from #Tmp drop table #Tmp

 

 

找出在表product中字段product_id和saler_id中重复的记录？  

 

  即product_id               saler_id  

  -----------------------------  

        22                                   aa  

        22                                   aa  

        33                                   aa  

        44                                   aa  

        55                                   bb  

        55                                   bb  

        66                                   bb  

        77                                   bb  

   

如何查出product_id重复的记录！

select   product_id   from   product  

group   by   product_id,saler_id   having   count(*)>1

 

 开发者博客：www.developsearch.com