删除重复的记录，但SQL中的第一条记录除外 [英] Remove duplicate records except the first record in SQL

问题描述

我要删除除第一个记录外的所有重复记录.

I want to remove all duplicate records except the first one.

赞:

NAME
R
R
rajesh
YOGESH
YOGESH

现在在上面，我要删除第二个"R"和第二个"YOGESH".

Now in the above I want to remove the second "R" and the second "YOGESH".

我只有一列名为"NAME".

I have only one column whose name is "NAME".

推荐答案

使用CTE(我在生产中有几种).

Use a CTE (I have several of these in production).

;WITH duplicateRemoval as (
    SELECT 
        [name]
        ,ROW_NUMBER() OVER(PARTITION BY [name] ORDER BY [name]) ranked
    from #myTable
    ORDER BY name
)
DELETE
FROM duplicateRemoval
WHERE ranked > 1;

说明:CTE将抓取您的所有记录，并为每个唯一条目应用行号.每个其他条目将获得一个递增的数字.用SELECT *替换DELETE，以查看其作用.

Explanation: The CTE will grab all of your records and apply a row number for each unique entry. Each additional entry will get an incrementing number. Replace the DELETE with a SELECT * in order to see what it does.

这篇关于删除重复的记录，但SQL中的第一条记录除外的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！