如何从SQL Server中的表中删除重复的行[重复]

This question already has an answer here:

这个问题在这里已有答案：

• How can I remove duplicate rows? 36 answers
• 如何删除重复的行？ 36个答案

I have a table called table1 which has duplicate values. It looks like this:

我有一个名为table1的表，它有重复的值。它看起来像这样：

new
pen
book
pen
like
book
book
pen

but I want to remove the duplicated rows from that table and insert them into another table called table2.

但是我想从该表中删除重复的行并将它们插入另一个名为table2的表中。

table2 should look like this:

table2应如下所示：

new 
pen
book
like

How can I do this in SQL Server?

我怎样才能在SQL Server中执行此操作？

3 个解决方案

#1

Let's assume the field was named name:

我们假设该字段命名为：

INSERT INTO table2 (name)
SELECT name FROM table1 GROUP BY name

that query would get you all the unique names.

该查询将为您提供所有唯一名称。

You could even put them into a table variable if you wanted:

如果您愿意，甚至可以将它们放入表变量中：

DECLARE @Table2 TABLE (name VARCHAR(50))

INSERT INTO @Table2 (name)
SELECT name FROM table1 GROUP BY name

or you could use a temp table:

或者您可以使用临时表：

CREATE TABLE #Table2 (name VARCHAR(50))

INSERT INTO @Table2 (name)
SELECT name FROM table1 GROUP BY name

#2

You can do this easily with a INSERT that SELECTs from a CTE where you use ROW_NUMBER(), like:

您可以使用从使用ROW_NUMBER（）的CTE中选择的INSERT轻松完成此操作，如：

DECLARE @YourTable table (YourColumn varchar(10))
DECLARE @YourTable2 table (YourColumn varchar(10))
INSERT INTO @YourTable VALUES ('new')
INSERT INTO @YourTable VALUES ('pen')
INSERT INTO @YourTable VALUES ('book')
INSERT INTO @YourTable VALUES ('pen')
INSERT INTO @YourTable VALUES ('like')
INSERT INTO @YourTable VALUES ('book')
INSERT INTO @YourTable VALUES ('book')
INSERT INTO @YourTable VALUES ('pen')

;WITH OrderedResults AS
(
SELECT
    YourColumn, ROW_NUMBER() OVER (PARTITION BY YourColumn ORDER BY YourColumn) AS RowNumber
    FROM @YourTable
)
INSERT INTO @YourTable2 
        (YourColumn)
    SELECT YourColumn FROM OrderedResults
        WHERE RowNumber=1

SELECT * FROM @YourTable2

SELECT * FROM @ YourTable2

OUTPUT:

OUTPUT：

YourColumn
----------
book
like
new
pen

(4 row(s) affected)

You can do this easily with a DELETE on a CTE where you use ROW_NUMBER(), like:

您可以在使用ROW_NUMBER（）的CTE上使用DELETE轻松完成此操作，如：

--this will just remove them from your original table
DECLARE @YourTable table (YourColumn varchar(10))
INSERT INTO @YourTable VALUES ('new')
INSERT INTO @YourTable VALUES ('pen')
INSERT INTO @YourTable VALUES ('book')
INSERT INTO @YourTable VALUES ('pen')
INSERT INTO @YourTable VALUES ('like')
INSERT INTO @YourTable VALUES ('book')
INSERT INTO @YourTable VALUES ('book')
INSERT INTO @YourTable VALUES ('pen')

;WITH OrderedResults AS
(
SELECT
    YourColumn, ROW_NUMBER() OVER (PARTITION BY YourColumn ORDER BY YourColumn) AS RowNumber
    FROM @YourTable
)
DELETE OrderedResults
    WHERE RowNumber!=1

SELECT * FROM @YourTable

OUTPUT:

OUTPUT：

YourColumn
----------
new
pen
book
like

(4 row(s) affected)

#3

I posted something on deleting duplicates a couple of weeks ago by using DELETE TOP X. Only for a single set of duplicates obviously. However in the comments I was given this little jewel by Joshua Patchak.

我几周前通过使用DELETE TOP X发布了删除重复项的内容。显然只有一组副本。然而在评论中，约书亚·帕查克给了我一颗小宝石。

;WITH cte(rowNumber) AS
(SELECT ROW_NUMBER() OVER (PARTITION BY [List of Natural Key Fields] 
ORDER BY [List of Order By Fields]) 
FROM dbo.TableName)

DELETE FROM cte WHERE rowNumber>1

This will get rid of all of the duplicates in the table.
Here is the original post if you want to read the discussion. Duplicate rows in a table.

这将消除表中的所有重复项。如果您想阅读讨论，这是原始帖子。表中的重复行。