SQLɾ³ýÖظ´Êý¾Ý

Ò»¡¢¾ßÓÐÖ÷¼üµÄÇé¿ö
I.¾ßÓÐΨһÐÔµÄ×Ö¶Îid(ΪΨһÖ÷¼ü)
delete Óû§±í   
where id not in   
(
select max(id) from Óû§±í group by col1,col2,col3...
)
group by ×Ó¾äºó¸úµÄ×ֶξÍÊÇÄãÓÃÀ´ÅжÏÖظ´µÄÌõ¼þ£¬ÈçÖ»ÓÐcol1£¬
ÄÇôֻҪcol1×Ö¶ÎÄÚÈÝÏàͬ¼´±íʾ¼Ç¼Ïàͬ¡£
II.¾ßÓÐÁªºÏÖ÷¼ü
¼ÙÉècol1+','+col2+','...col5 ΪÁªºÏÖ÷¼ü
£¨ÕÒ³öÏàͬ¼Ç¼£©
select * from    Óû§±í where col1+','+col2+','...col5 in
(
select max(col1+','+col2+','...col5) from Óû§±í   
group by col1,col2,col3,col4
having count(*)>1    
)
group by ×Ó¾äºó¸úµÄ×ֶξÍÊÇÄãÓÃÀ´ÅжÏÖظ´µÄÌõ¼þ£¬
ÈçÖ»ÓÐcol1£¬ÄÇôֻҪcol1×Ö¶ÎÄÚÈÝÏàͬ¼´±íʾ¼Ç¼Ïàͬ¡£
»òÕߣº
£¨ÕÒ³öÏàͬ¼Ç¼£©
select * from Óû§±í    where exists (select 1 from Óû§±í x where Óû§±í.col1 = x.col1 and   
Óû§±í.col2= x.col2 group by x.col1,x.col2 having count(*) >1)
III:ÅжÏËùÓеÄ×Ö¶Î
   select * into #aa from Óû§±í group by id1,id2,....
   delete Óû§±í   
   insert into Óû§±í select * from #aa
¶þ¡¢Ã»ÓÐÖ÷¼üµÄÇé¿ö
I.ÓÃÁÙʱ±íʵÏÖ
select identity(int,1,1) as id,* into #temp from Óû§±í
delete #temp   
where id not in  
(
   select max(id) from # group by col1,col2,col3...
)
delete Óû§±í ta
inset into ta(...) select ..... from #temp
II.Óøıä±í½á¹¹£¨¼ÓÒ»¸öΨһ×ֶΣ©À´ÊµÏÖ
alter Óû§±í add    newfield int identity(1,1)
delete Óû§±í
where newfield not in
(
select min(newfield) from Óû§±í group by ³ýnewfieldÍâµÄËùÓÐ×Ö¶Î
)
alter Óû§±í drop column newfield