从数据存储中删除重复条目，同时保留一个副本

引言

这里解释的代码将展示如何从数据存储中删除重复条目，同时保留一份副本。代码首先会创建一个包含重复记录的临时表，字段为'FullName'，然后获取需要删除的记录的ID，最后删除这些记录。

使用代码

以下是完整的SQL代码

           --Create Temp Table
IF OBJECT_ID('TempDup') IS NOT NULL
DROP TABLE 'TempDup'
GO
CREATE TABLE [dbo].[TempDup]
(
    [ID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_TempDup_ID] DEFAULT (newid()),
    [FullName] [nchar](10) NOT NULL,
    CONSTRAINT [PK_TempDup] PRIMARY KEY CLUSTERED ( [ID] ASC )ON [PRIMARY]
) ON [PRIMARY]
GO
 INSERT INTO TempDup VALUES   ( NEWID(), 'N1')
 INSERT INTO TempDup VALUES   ( NEWID(), 'N2')
 INSERT INTO TempDup VALUES   ( NEWID(), 'N2')
 INSERT INTO TempDup VALUES   ( NEWID(), 'N2')
 INSERT INTO TempDup VALUES   ( NEWID(), 'N3')
 INSERT INTO TempDup VALUES   ( NEWID(), 'N3')
-- This code will select the Duplicate row only and keep single copy from row
SELECT [ID], [FullName], [RowIndex]
FROM
(
    SELECT 
        [ID], [FullName], RANK() OVER (PARTITION BY [FullName] 
        ORDER BY [ID] ASC) AS [RowIndex]
    FROM [dbo].[TempDup]
)[T1]
WHERE [T1].[RowIndex] > 1
GO
-- If replace SELECT SQL command with DELETE SQL command
DELETE FROM [dbo].[TempDup] WHERE [ID] IN
(
    SELECT [ID] FROM
    (
        SELECT 
        [ID], [FullName],
         RANK() OVER (PARTITION BY [FullName] ORDER BY [ID] ASC) AS [RowIndex]
        FROM [dbo].[TempDup]
    )[T1] WHERE [T1].[RowIndex] > 1
)
GO