删除表中的重复行 [英] Delete Duplicate Rows in Table

问题描述

我有1个表Name是NameInfo，在NameInfo内部我有1列.
列名称为名称"，如下所示

表格:-NameInfo
列:-名称

我有以下数据

名称:
詹姆斯
詹姆斯
迈克
大卫
詹姆斯
迈克
大卫

现在我只想使用1个查询删除重复行
因此，输出将为

名称:
詹姆斯
迈克
David

I have 1 table Name is NameInfo and inside NameInfo i have 1 column.
the column name is Name like following

Table :- NameInfo
Column :- Name

I have Following Data

Name:
James
James
Mike
David
James
Mike
David

Now i want to delete Duplicate Rows using only 1 query
so, output will be

Name:
James
Mike
David

推荐答案

亲爱的朋友，

以下代码对于删除重复记录很有用.

Dear Friend,

Following code is useful to delete duplicate records.

WITH CTE (COl1, DuplicateCount)
AS
(
SELECT COl1,
ROW_NUMBER() OVER(PARTITION BY COl1 ORDER BY Col1) AS DuplicateCount
FROM DuplicateRcordTable
)
DELETE
FROM CTE
WHERE DuplicateCount > 1
GO

谢谢

Thanks

关注此链接

http://blog.sqlauthority.com/2007/03/01 /sql-server-delete-duplicate-records-rows/ [

follow this link

http://blog.sqlauthority.com/2007/03/01/sql-server-delete-duplicate-records-rows/[^]

您好，请在下面的查询中使用

Hi, use below query

Select *, ROW_NUMBER() over (order by (select 0)) as RowNum into #tran1 from table1
Select * from #tran1
Delete from #tran1 where RowNum not in (Select MIN(RowNum) from #tran1 group by UName)
Select * from #tran1

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