SQL 删除指定字段重复的记录 

 1 /*删除指定字段重复的记录，保留RowID最小的记录*/
 2     /*取出重复的数据放到临时表*/
 3 SELECT MIN(RowID) AS RowID,field1,field2 INTO T1 FROM tb GROUP BY field1,field2 HAVING COUNT(*)>1
 4     /*删除记录*/
 5 DELETE tb FROM tb T
 6     WHERE 1=1
 7     AND EXISTS( SELECT * FROM T1 WHERE T.field1 = T1.field1 AND T.field2 = T1.field2 )
 8     AND NOT EXISTS( SELECT * FROM T1 WHERE T.RowID = T1.RowID )
 9     /*删除临时表*/
10 DROP TABLE T1

MSSQL 测试通过

posted @ 2020-07-27 16:02  锁钥  阅读(338)  评论(0)  编辑  收藏  举报