使用 SQL Server 分区删除重复项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9122468/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Removing duplicates using partition by SQL Server
提问by l--''''''---------''''''''''''
I need to remove duplicates from a table:
我需要从表中删除重复项：
;WITH cte as(
SELECT ROW_NUMBER() OVER (PARTITION BY [specimen id]
ORDER BY ( SELECT 0 ) ) RN
FROM quicklabdump)
delete from cte where RN>1
The column quicklabdumpIDis the primary key.
该列quicklabdumpID是主键。
I would like to know how to keep only the largest quicklabdumpIDwhere there are multiple occurrences of [specimen id]
我想知道如何quicklabdumpID在多次出现的情况下只保留最大的[specimen id]
回答by Mikael Eriksson
Change your order by to quicklabdumpid DESC.
将您的订单更改为quicklabdumpid DESC。
WITH cte as(
SELECT ROW_NUMBER() OVER (PARTITION BY [specimen id]
ORDER BY quicklabdumpid DESC ) RN
FROM quicklabdump)
delete from cte where RN>1
回答by Clint Good
No need for partition
不需要分区
delete q
from quicklabdump q
where exists
(
select *
from quicklabdump q2
where q2.[specimen id] = q.[specimen id] and
q2.quicklabdumpID > q.quicklabdumpID
)
