我在表格中有重复的行,如何基于单个列的值删除它们?
例如:
uniqueid, col2, col3 ...
1, john, simpson
2, sally, roberts
1, johnny, simpson
delete any duplicate uniqueIds
to get
1, John, Simpson
2, Sally, Roberts
我在表格中有重复的行,如何基于单个列的值删除它们?
例如:
uniqueid, col2, col3 ...
1, john, simpson
2, sally, roberts
1, johnny, simpson
delete any duplicate uniqueIds
to get
1, John, Simpson
2, Sally, Roberts
你可以从一个CTE中使用DELETE
命令:
WITH cte AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY uniqueid ORDER BY col2)'RowRank'
FROM Table)
DELETE FROM cte
WHERE RowRank > 1
ROW_NUMBER()
函数为每一行分配一个编号。使用 PARTITION BY
可以为每个组中的每个项目重新开始编号,这里的每个唯一标识符 uniqueid
的值都将从 1 开始编号并逐渐增加。使用 ORDER BY
确定编号的顺序。由于每个 uniqueid
都从 1 开始编号,因此具有大于 1 的 ROW_NUMBER()
的任何记录都具有重复的 uniqueid
。ROW_NUMBER()
函数的工作原理,只需试用它即可。SELECT *,ROW_NUMBER() OVER(PARTITION BY uniqueid ORDER BY col2)'RowRank'
FROM Table
ORDER BY uniqueid
ROW_NUMBER()
函数的逻辑来决定保留或删除哪些记录。PARTITION BY
中:WITH cte AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY uniqueid, col3 ORDER BY col2)'RowRank'
FROM Table)
DELETE FROM cte
WHERE RowRank > 1
ROW_NUMBER()
的描述。 - Hart CODELETE
更改为 SELECT
,以查看将受到 DELETE
影响的所有记录。您还可以使用 OUTPUT
子句将已删除的行输出到另一个表中:DELETE FROM cte OUTPUT DELETED.* INTO #Deleted WHERE RowRank > 1
- Hart COrowId |uniqueid |col2 |col3
----- |-------- |---- |----
1 10 john simpson
2 20 sally roberts
3 10 johnny simpson
您可以通过按照应该是唯一的事情进行分组(无论是一个列还是多个列),然后从每个组中获取rowId,并删除除这些rowIds之外的所有内容来移除重复项。在内部查询中,表中的所有内容都将具有rowId,除了重复行。
select *
--DELETE
FROM MyTable
WHERE rowId NOT IN
(SELECT MIN(rowId)
FROM MyTable
GROUP BY uniqueid);
您也可以使用 MAX 而不是 MIN,结果会类似。
select * into NewTable from ExistingTable
union
select * from ExistingTable;
DECLARE @du TABLE (
id INT,
Name VARCHAR(4)
)
INSERT INTO @du VALUES(1,'john')
INSERT INTO @du VALUES(2,'jane')
INSERT INTO @du VALUES(1,'john')
;WITH dup (id,dp)
AS
(SELECT id
, ROW_NUMBER() OVER(PARTITION BY id ORDER BY Name) AS dp
FROM @du)
DELETE FROM dup
WHERE dp > 1
SELECT *
FROM @du
with CTE(DuplicateCount) as ( SELECT ROW_NUMBER() OVER
(PARTITION by UniqueId order by UniqueId ) as DuplicateCount from
Table1 ) Delete from CTE where DuplicateCount > 1
.Without using CTE*
Delete DuplicateCount from ( Select Row_Number() over(Partition by
UniqueId order by UniqueId) as Dup from Table1 ) DuplicateCount
where DuplicateCount.Dup > 1
.Without using row_Number() and CTE
Delete from Subject where RowId not in(select Min(RowId ) from
Subject group by UniqueId)
DELETE FROM table WHERE uniqueid='1' AND col2='john'
或者将col2='john'
改为col2='johnny'
,取决于您要删除哪个记录。
您是如何在第一次中获得两个相同的“唯一”ID的?