以下是tSQL查询：
DECLARE @table TABLE(data VARCHAR(20))
INSERT INTO @table VALUES ('not duplicate row')
INSERT INTO @table VALUES ('duplicate row')
INSERT INTO @table VALUES ('duplicate row')
INSERT INTO @table VALUES ('second duplicate row')
INSERT INTO @table VALUES ('second duplicate row')
SELECT data
INTO #duplicates
FROM @table
GROUP BY data
HAVING COUNT(*) > 1
-- delete all rows that are duplicated
DELETE FROM @table
FROM @table o INNER JOIN #duplicates d
ON d.data = o.data
-- insert one row for every duplicate set
INSERT INTO @table(data)
SELECT data
FROM #duplicates
我理解它在做什么,但逻辑的最后一部分(在每个重复集合后插入一行)没有意义.我们有一组代码–delete所有重复的行,那就删除了重复项,那么最后一部分是什么？
谢谢
解决方法
Where we have the set of code for –delete all rows that are duplicated,that gets rid of the duplicates so what’s the part of the last section?
首先,它删除所有曾经重复的行.也就是说,所有行,原始也是.在上面的例子中,DELETE之后只有一行(‘不重复行’)将保留在表中.其他所有四行都将被删除.
最好的方法是：
WITH q AS (
SELECT data,ROW_NUMBER() OVER (PARTITION BY data ORDER BY data) AS rn
FROM @table
)
DELETE
FROM q
WHERE rn > 1
