当前位置:操作系统 > Unix/Linux >>

删除数据表中的重复数据

删除数据表中的重复数据
 
示例表结构如下:
[sql] 
MyTable  
-----------  
RowID int not null identity(1,1) primary key,  
Col1 varchar(20) not null,  
Col2 varchar(2048) not null,  
Col3 tinyint not null  
解决方法:
[sql] 
DELETE FROM TableName  
WHERE  ID NOT IN (SELECT MAX(ID)  
                  FROM   TableName  
                  GROUP  BY Column1,  
                            Column2,  
                            Column3  
                  /*Even if ID is not null-able SQL Server treats MAX(ID) as potentially  
                    nullable. Because of semantics of NOT IN (NULL) including the clause  
                    below can simplify the plan*/  
                  HAVING MAX(ID) IS NOT NULL)   
如果是复合主键的话,需要把整个子查询放在EXISTS子句中,EXISTS用法如下:
 
[sql] 
DELETE FROM agent1 da    
 WHERE EXISTS(    
  SELECT * FROM  customer cu    
   WHERE grade=3    
     AND da.agent_code<>cu.agent_code);   
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,