查询重复记录和对重复记录操作

 

 

 
 ---查出重复记录
 select a.* from account a where a.acc_first_name in
  (select acc_first_name from (
                select count(a.acc_first_name) num,a.acc_first_name from account a group by a.acc_first_name)
         a where num >=2)
        
   select a.* from account a where a.acc_first_name in     
      (select a.acc_first_name from account a group by a.acc_first_name having count(a.acc_first_name) >=2)

        
   
 ----对重复记录进行删除，只保留一条
 delete from account where rowid in (
  select min(rowid) from account ac where ac.acc_first_name in(
         select acc_first_name from (
                select count(a.acc_first_name) num,a.acc_first_name from account a group by a.acc_first_name)
         a where num >=2
   )
 )
 
  delete  from account where rowid in (
  select min(rowid) from account ac where ac.acc_first_name in(
         select acc_first_name from (
                select a.acc_first_name from account a group by a.acc_first_name having count(a.acc_first_name) >=2)
   )
 )