sqlserver查询和册除重复数据-亲测可用

查询重复数据

SELECT * from KS_U_shangbaoguanli_copy where sfznum in (SELECT sfznum FROm KS_U_shangbaoguanli_copy GROUP BY sfznum having count(sfznum)>1)

删除重复数据只保存ID最小的一条

DELETE FROM KS_U_shangbaoguanli_copy where sfznum in (SELECT sfznum FROM KS_U_shangbaoguanli_copy GROUP BY sfznum HAVING count(sfznum)>1) and InfoID not IN (SELECT MIN(InfoID) from KS_U_shangbaoguanli_copy GROUP BY sfznum HAVING count(sfznum)>1)