如何删除重复项 [英] how to delete duplicates

问题描述

行。我有一个带有一些重复记录（行）的表。



示例：employeeID 1001在表中是两次，每列都有相同的数据，但ColumnX不同。所以它看起来像这样。 ColumnX在表格中显示空白和其他一个显示。



我如何才能删除空白的那个？



1001 ColumnX





1001 7





谢谢。

OK. I have a table that with some duplicate records (Rows).

Example: employeeID 1001 is twice in the table with every column having the same data except ColumnX is different. So it looks like this. ColumnX shows blank and other one show 7 in the table.

How can I ONLY delete the one that is blank?

1001 ColumnX


1001 7


Thanks.

推荐答案

本文可能对您删除重复的行有所帮助 https://support.microsoft.com/ en-us / kb / 139444

This article might be helpful for you to delete duplicate rows https://support.microsoft.com/en-us/kb/139444

您可以使用cte删除重复记录。

请参阅以下帖子

you can delete duplicate records using cte.
Refer below post

delete duplicate records in sql server

-- A cut down version of the problem.
create table duplicates
(
 id varchar(10),
 colX varchar(10) null
)

insert into duplicates (id, colX) values ('1001','7')
insert into duplicates (id, colX) values ('1001',null)
insert into duplicates (id, colX) values ('1001',null)
insert into duplicates (id, colX) values ('1002','8')
insert into duplicates (id, colX) values ('1002',null)
insert into duplicates (id, colX) values ('1003','9')

并识别要删除的行....

And to identify rows to delete....

-- Identify duplicate ID values
-- Using a table variable but it could be a temp table.
declare @duplicateKey table (key1 varchar(10))

insert into @duplicateKey (key1)
select id from duplicates group by id having count(id) > 1

现在检查我们的列表中只有1001,1002删除候选人。

Now check we have only 1001, 1002 in our list of deletion candidates.

select * from @duplicateKey 

(2 row(s) affected)
key1
----------
1001
1002

-- Delete rows with identified IDs and "invalid" columns 

delete duplicates
from duplicates 
inner join @duplicateKey as deleteList
      on deleteList.key1 = duplicates.id 
      and duplicates.colX is null

-- Prove that we have only "valid" rows.
select * from duplicates

id         colX
---------- ----------
1001       7
1002       8
1003       9

但是如上所述，如果可能的话，最好先防止重复。

However as was pointed out above you're better off preventing duplicates in the first place if at all possible.

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