sql - 删除 1 个重复值的实例 T-SQL

Question

这个问题在这里已经有了答案:





How can I remove duplicate rows?

(41 个回答)


7年前关闭。




我目前有一张名为 People 的表。在此表中，有数千行数据遵循以下布局:

gkey |    Name |  Date       | Person_Id
1    |    Fred |  12/05/2012 | ABC123456
2    |    John |  12/05/2012 | DEF123456
3    |    Dave |  12/05/2012 | GHI123456
4    |    Fred |  12/05/2012 | JKL123456
5    |    Leno |  12/05/2012 | ABC123456

如果我执行以下操作:

SELECT [PERSON_ID], COUNT(*) TotalCount
FROM [Database].[dbo].[People]
GROUP BY [PERSON_ID]
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

我得到的返回:

Person_Id     | TotalCount
ABC123456     | 2

现在我只想删除一行重复值，因此当我执行上述查询时，我没有返回任何结果。这可能吗？

Answer 1

WITH a as
(
SELECT row_number() over (partition by [PERSON_ID] order by name) rn
FROM [Database].[dbo].[People]
)
DELETE FROM a
WHERE rn = 2