1、数据准备：生成重复数据SELECT name,object_id,type,type_desc,create_date,is_ms_shipped into temp_test FROM sys.objectsinsert into temp_testSELECT top 30 name,object_id,type,type_desc,create_date,is_ms_shipped FROM sys.objects
2、验证是否有重复数据方法一：select name,object_id,type,type_desc,create_date,is_ms_shipped,count(*) from temp_testgroup by name,object_id,type,type_desc,create_date,is_ms_shipped having count(*) >1
3、验证是否有重复数据方法二：衡痕贤伎select * from (SELECT name,object_id,type,type_desc,cre锾攒揉敫ate_date,is_ms_shipped,ROW_NUMBER()over(partition by name,object_id,type,type_desc,create_date,is_ms_shipped order by name) as rk FROM temp_test ) t where t.rk > 1
4、删除重复数据方法一：创建临时表-- 取去重的数据到备份表select distinct name,obje艘早祓胂ct_id,type,type_desc,create_date,is_ms_shipped into temp_test_new from temp_test-- 删除重复数据表drop table if exists temp_test-- 修改备份表名为原来表名EXEC sp_rename 'temp_test_new', 'temp_test'
5、删除重复数据方法二：使用“with子句和ROW_NUMBER()函数”来实现删除完全重复的其他行WITH tab AS( SELECT ROW_NUMBER()over(partition by object_id order by object_id) as rk FROM temp_test)DELETE FROM tab WHERE rk>1
