数据库批量删除

案例

same.png

语句

查找相同的数据

SELECT  *
FROM    Test.dbo.test a
WHERE   EXISTS ( SELECT *
                 FROM   Test.dbo.test
                 WHERE a.name=name
                 GROUP BY name ,dep
                 HAVING COUNT(*) > 1 ); 

去重方法

DELETE  FROM Test.dbo.Test
WHERE   ID NOT IN ( SELECT  MIN(ID)
                    FROM    Test.dbo.Test
                    GROUP BY Name ,Dep );

OR

DELETE
FROM    Test.dbo.Test
WHERE   ID NOT IN ( SELECT  MAX(ID)
                    FROM    Test.dbo.Test
                    GROUP BY Name ,Dep )