我有一个名为table1
的表格,其中存在重复值。它看起来像这样:
new
pen
book
pen
like
book
book
pen
但我想从表格中删除重复的行,并将它们插入到另一个名为 table2
的表格中。
table2
应该像这样:
new
pen
book
like
我该如何在SQL Server中实现这个功能?
我有一个名为table1
的表格,其中存在重复值。它看起来像这样:
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
,从CTE
中进行SELECT
,并使用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
从@YourTable2中选择所有内容
输出:
YourColumn
----------
book
like
new
pen
(4 row(s) affected)
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
YourColumn
----------
new
pen
book
like
(4 row(s) affected)
我几周前发布了一篇关于使用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
c#
标签,是因为...?你到目前为止尝试了什么?请阅读 [FAQ] 和 [ask]。 - Soner Gönülnew
和like
在table
中是在哪里重复的? - HABO