删除重复数据6种方法

1、如果有ID字段，就是具有唯一性的字段  

delect   table   where   id   not   in   (     

select   max(id)   from   table   group   by   col1,col2,col3...     

)     

group   by   

子句后跟的字段就是你用来判断重复的条件，如只有col1，那么只要col1字段内容相同即表示记录相同。 

2、如果是判断所有字段也可以这样  

select   *   into   #aa   from   table   group   by   id1,id2,....     

delete   table       

insert   into   table       

select   *   from   #aa    

3、没有ID的情况  

select   identity(int,1,1)   as   id,*   into   #temp   from   tabel     

delect   #   where   id   not   in   (     

select   max(id)   from   #   group   by   col1,col2,col3...)     

delect   table     

inset   into   table(...)     

select   .....   from   #temp   

4、 col1+','+col2+','...col5 联合主键  

select   *   from     table   where   col1+','+col2+','...col5   in   (     

select   max(col1+','+col2+','...col5)   from   table       

where   having   count(*)>1     

group   by   col1,col2,col3,col4       

)    

group   by   

子句后跟的字段就是你用来判断重复的条件，如只有col1，那么只要col1字段内容相同即表示记录相同。 

5、select   identity(int,1,1)   as   id,*   into   #temp   from   tabel     

select   *   from     #temp   where   id   in   (     

select   max(id)   from   #emp   where   having   count(*)>1   group   by   col1,col2,col3...)  

6、select   distinct   *   into   #temp   from   tablename       

delete   tablename       

go     

insert   tablename   select   *   from   #temp   Sqlclub   

go     

drop   table   #temp