使用 SQL Server 在表中查找重复记录

问题描述：

我正在验证一个包含电子商务网站交易级别数据的表格，并找出确切的错误.

I am validating a table which has a transaction level data of an eCommerce site and find the exact errors.

我需要您的帮助，以便在 SQL Server 上的 50 列表中查找重复记录.

I want your help to find duplicate records in a 50 column table on SQL Server.

假设我的数据是:

OrderNo shoppername amountpayed city Item       
1       Sam         10          A    Iphone
1       Sam         10          A    Iphone--->>Duplication to be detected
1       Sam         5           A    Ipod
2       John        20          B    Macbook
3       John        25          B    Macbookair
4       Jack        5           A    Ipod

假设我使用以下查询:

Select shoppername,count(*) as cnt
from dbo.sales
having count(*) > 1
group by shoppername

会回来的

Sam  2
John 2

但我不想找到超过 1 或 2 列的重复项.我想在我的数据中找到所有列的重复项.我希望结果为:

But I don't want to find duplicate just over 1 or 2 columns. I want to find the duplicate over all the columns together in my data. I want the result as:

1       Sam         10          A    Iphone

答

with x as   (select  *,rn = row_number()
            over(PARTITION BY OrderNo,item  order by OrderNo)
            from    #temp1)

select * from x
where rn > 1

您可以通过将 select 语句替换为

you can remove duplicates by replacing select statement by

delete x where rn > 1

使用 SQL Server 在表中查找重复记录

相关推荐