mssql查询和删除表中重复的数据（根据唯一列值）

-- 临时表方法：

SELECT IDENTITY(INT,1,1) AS autoID ,* INTO #TEMP1 FROM tableName

SELECT MIN(autoID) AS autoID INTO #TEMP2  FROM #TEMP1 GROUP BY columnName

--查询重复的数据

SELECT * FROM tableName WHERE 主键 IN (SELECT 主键 FROM #TEMP1 WHERE autoID IN(SELECT autoID FROM #TEMP2))

--删除重复数据

 

DELETE FROM tableName WHERE 主键  IN

（

    SELECT 主键 FROM #TEMP1 WHERE autoID NOT IN (SELECT autoID FROM #TEMP2)

）