mysql - 如何删除基于非主键列的重复行

Question

所以我设置了一个表模式: http://sqlfiddle.com/#!6/62d3e/1/0

我有几行按字段、字段类型 ID、字段别名列重复。我需要删除所有与这 3 列重复的行，但“somevalue”列具有空值。

最简单的方法是什么？我尝试执行 MERGE 语句，但这不起作用，因为合并会捕获重复项。

DECLARE @A TABLE(Id INT, field NVARCHAR(50), fieldType INT, fieldalias NVARCHAR(50), ForDelete BIT)
 DECLARE @B TABLE(Id INT, field NVARCHAR(50), fieldTypeId INT, fieldalias NVARCHAR(50), ForDelete BIT)

 INSERT INTO @A
 SELECT DISTINCT c.Id, c.field, c.fieldTypeId, c.fieldalias,0
 FROM DuplicateValues c
 WHERE c.somevalue IS NOT NULL

 INSERT INTO @B
 SELECT DISTINCT c.Id, c.field, c.fieldTypeId, c.fieldalias,0
 FROM DuplicateValues c
 WHERE c.somevalue IS NULL

 declare @T table(Id int, ForDelete BIT, Act varchar(10))


 MERGE @B AS B
 USING @A AS A
 ON A.fieldTypeId = B.fieldTypeId AND A.field = B.field AND A.fieldalias = B.fieldalias
 WHEN MATCHED THEN UPDATE SET B.ForDelete = 1
 WHEN NOT MATCHED THEN INSERT (Id, field, fieldTypeId, fieldalias, ForDelete) VALUES(A.Id, A.field,A.fieldTypeId, A.fieldalias, 1)
 OUTPUT INSERTED.Id, INSERTED.ForDelete, $ACTION INTO @T;


 SELECT * FROM @T

Answer 1

根据您提供的 Fiddle 架构，您可以使用以下查询来实现相同的目的。查看您修改后的 fiddle http://sqlfiddle.com/#!6/0f201/1

delete from DuplicateValues
where FIELD in(
  select FIELD from DuplicateValues
  group by field, fieldtypeId, fieldalias
  having count(*) > 1
  )
and somevalue is null

此处 group by .. having count(*) > 1 将给出所有已重复的 FIELDS。