运用sql server 提供的系统 ROW_NUMBER() ,和 RANK() 函数清除重复数据
希望对你有用,
例子如下
--清除重复数据
WITH DATA
AS ( SELECT ROW_NUMBER() OVER ( ORDER BY ProjectID ) ROW ,
RANK() OVER ( ORDER BY ProjectID ) RAN ,
*
FROM 表名称
WHERE ProjectID IN ( SELECT ProjectID
FROM 表名称
GROUP BY ProjectID
HAVING COUNT(1) > 1 )
)
DELETE 表名称
WHERE 主键列名称
IN ( SELECT 主键列名称
FROM DATA
WHERE ROW <> RAN )