【转】SQL删除重复记录,只保留其中一条

* from people where peopleId in ( select peopleId from people group by peopleId having count (peopleId) > 1 )

2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录

 delete   from  people  where    peopleName  in  ( select  peopleName     from  people  group   by  peopleName       having   count (peopleName)  >   1 )  and    peopleId  not   in  ( select   min (peopleId)  from  people  group   by  peopleName      having   count (peopleName) >  1 )

3、查找表中多余的重复记录（多个字段）

 select   *   from  vitae a  where  (a.peopleId,a.seq)  in  ( select  peopleId,seq  from  vitae  group   by  peopleId,seq  having   count ( * )  >   1 )

4、删除表中多余的重复记录（多个字段），只留有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 )

5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录 

 select   *   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 )  

6.消除一个字段的左边的第一位：

 update  tableName  set   [  Title  ]  =Right ( [  Title  ] ,( len ( [  Title  ] ) -  1 ))  where  Title  like   ‘  村%  ‘ 

7.消除一个字段的右边的第一位：

 update  tableName  set   [  Title  ]  =left ( [  Title  ] ,( len ( [  Title  ] ) -  1 ))  where  Title  like   ‘  %村  ‘ 

8.假删除表中多余的重复记录（多个字段），不包含rowid最小的记录

  update  vitae  set  ispass =-  1   where  peopleId  in  ( select  peopleId  from  vitae  group   by  peopleId,seq  having   count ( * )  >   1 )  and  seq  in  （ select  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 )    

 

本文转自：http://www.cnblogs.com/huangw/archive/2012/06/04/2534676.html

【转】SQL删除重复记录,只保留其中一条

标签：

查看更多关于【转】SQL删除重复记录,只保留其中一条的详细内容...