資料庫批量删除

案例

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 )