如何从SQL Server中的表中删除重复的行
问题描述:
我有一个名为table1
的表具有重复值。它看起来像这样:如何从SQL Server中的表中删除重复的行
new
pen
book
pen
like
book
book
pen
但我想从该表中删除重复的行并将其插入到被称为table2
另一个表。
table2
应该是这样的:
new
pen
book
like
我怎样才能做到这一点在SQL Server?
答
假设领域被评为name
:
INSERT INTO table2 (name)
SELECT name FROM table1 GROUP BY name
该查询会得到大家的唯一名称。
你甚至可以把它们放在一个表变量,如果你想:
DECLARE @Table2 TABLE (name VARCHAR(50))
INSERT INTO @Table2 (name)
SELECT name FROM table1 GROUP BY name
或者你可以使用一个临时表:
CREATE TABLE #Table2 (name VARCHAR(50))
INSERT INTO @Table2 (name)
SELECT name FROM table1 GROUP BY name
答
您可以用INSERT
是SELECT
就做这个很容易从CTE
您使用ROW_NUMBER()
,如:
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
OUTPUT:
--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:
YourColumn
----------
book
like
new
pen
(4 row(s) affected)
您可以在CTE
在您使用ROW_NUMBER()
,就像一个DELETE
做到这一点很容易
YourColumn
----------
new
pen
book
like
(4 row(s) affected)
答
我发布了一些事情g几个星期前通过使用DELETE TOP X来删除重复项。显然,仅对于一组重复项。然而在评论中,我被Joshua Patchak给了这个小小的宝石。
;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
这将摆脱表中的所有重复项。
如果你想阅读讨论,这里是原文。 Duplicate rows in a table.
您对此问题使用了'c#'标签,因为..?你到目前为止尝试过什么?阅读[FAQ]和[问] – 2013-02-27 18:50:51
'new'和'like'在'table'中被复制到哪里? – HABO 2013-02-27 19:46:57
这个问题不是链接问题的重复,因为a。与链接问题不同,此表没有主键(这会否定链接问题中的大多数解决方案),以及b。它正在将数据传输到另一个表。 – user281806 2013-10-12 00:46:03