如何转换SQL语句"delete from TABLE where someID not in (select someID from Table group by property1, property2)"

Question

我正在尝试将以下SQL语句转换为Core Data：

delete from SomeTable
where someID not in (
    select someID
    from SomeTable
    group by property1, property2, property3
)

基本上，我想在一个表中检索和删除可能的重复记录。如果property1、property2和property3与另一条记录相等，则认为该记录是重复的。

我该如何做呢？

PS：正如标题所述，我正在尝试将上面的SQL语句转换为iOS Core Data方法，而不是试图改进、纠正或评论上面的SQL，这已经超出了重点。

谢谢。

- nemesys

---

您的起始查询似乎没有按照您所描述的那样执行。您是要求更好的SQL查询，还是要求与Core Data相关的特定内容？ - John Bollinger

此外，我们是否可以理解为列 SomeTable.someId 是唯一的或者（更好的）是主键？ - John Bollinger

嘿约翰。我在iOS项目中使用了原始SQL来接手这个项目。我不想深入挖掘，所以当我重写应用程序时，我选择了Core Data作为后端。现在，我无法确定此方法是否返回了它应该返回的内容，但这是用于删除具有相同三个属性值（例如property1，property2和property3）的记录的重复项的方法。我正在研究如何将其转换为Core Data。哦，另外SomeTable.someID是唯一的。 - nemesys

MySQL是我所知道的唯一一个能够接受这种语句的数据库管理系统。在这种情况下，结果将是删除除每组重复项之外的所有内容。MySQL不定义将保留每个组中的哪一行。然而，这不是我理解你的请求的方式。我会尽快更新我的答案。 - John Bollinger

Answer 1

听起来你需要用SQL来完成你的目标。你的起始查询不会做你描述的事情，大多数数据库都不会接受它，因为聚合子查询试图选择一个不是组函数的列。

更新：

我最初认为请求是删除每个包含重复项的组中的所有成员，并相应地编写了代码。重新解释原始SQL，就像MySQL会做的那样，似乎目标是保留每个（property1，property2，property3）组合的一个元素。我想这更有意义。以下是一种标准方法：

delete from SomeTable st1
where someID not in (
    select min(st2.someId)
    from SomeTable st2
    group by property1, property2, property3
  )

使用min()聚合函数来选择每个组中要保留的someId值，这与原始内容有所不同。这也应该是有效的：

delete from SomeTable st1
where someID in (
  select st3.someId
  from SomeTable st2
    join SomeTable st3
      on st2.property1 = st3.property1
        and st2.property2 = st3.property2
        and st2.property3 = st3.property3
  where st2.someId < st3.someId
)

这两个查询将保留相同的行。我更喜欢第二个，尽管它更长，因为对于从大型集合中选择少量元素来说，NOT IN运算符有点难搞。但是，如果您预计会有足够多的行涉及到缩放问题，那么您应该尝试两种方法，并考虑优化（例如，在(property1，property2，property3)上创建索引）和其他替代方案。

关于在Core Data调用术语中编写它，我认为你不能完全做到。Core Data支持分组，因此您可以编写Core Data调用以执行第一种选择中的子查询并返回实体对象或其ID，如所述进行分组。然后您可以遍历这些组，跳过每个组的第一个元素，并为所有其他元素调用Core Data删除方法。详细信息超出了SO格式的范围。

然而，我必须说，在Core Data中执行这样的工作比直接在数据库中执行要昂贵得多，无论是时间还是需要的内存。直接在数据库中执行此操作对ORM框架（如Core Data）不友好。这种情况是使用ORM框架所做的折衷之一。

我建议您尽可能避免这种需求。在SomeTable(property1, property2, property3)上定义唯一索引，并尽可能避免尝试创建重复项或从（失败的）尝试中恢复。

- John Bollinger

---

有些人在SQL方面比我强得多！我该如何将其转换为适当的Core Data调用？ - nemesys

感谢您抽出时间回复，John。我显然不能直接从数据库中删除行。那显然会破坏核心数据。我将研究如何使用您的逻辑来得出在核心数据中可接受的解决方案。 - nemesys

Answer 2

DELETE SomeTable 
FROM SomeTable
LEFT OUTER JOIN (
   SELECT MIN(RowId) as RowId, property1, property2, property3 
   FROM SomeTable 
   GROUP BY property1, property2, property3
) as KeepRows ON
   SomeTable.RowId = KeepRows.RowId
WHERE
   KeepRows.RowId IS NULL

- Deep Kalra

Answer 3

在iOS中执行此操作时，以下是一些提示： 在iOS 9之前，唯一的删除对象的方法是逐个删除，即您需要迭代重复数组并删除每个重复项。(如果您针对的是iOS9，则有一个新的NSBatchDeleteRequest可以帮助您一次性删除它们- 它确实直接作用于存储，但也会进行一些清理，例如确保必要的关系得到更新)。另一个问题是识别重复项。您可以配置获取其结果的群组(请参阅NSFetchRequest的propertiesToGroupBy)，但您必须指定NSDictionaryResultType(因此结果不是对象本身，而只是相关属性的值)。此外，CoreData不允许您获取未在GROUP BY中指定的属性(除聚合外)。 因此，使用min(someId)的建议(在其他答案中)将是必要的。(要获取此类表达式，您需要使用NSExpression，将其嵌入NSExpressionDescription中，并将后者传递给获取请求的propertiesToFetch)。

最终结果将是一个字典数组，每个字典都保存着您的主记录（即您不想删除的记录）的someId值，然后您需要找出其中的重复项。有各种方法，但没有一种方法会非常高效。

因此，正如其他答案所说，最好在第一时间避免重复。在这方面，请注意iOS 9允许您指定您希望是唯一的属性（单个或集体）。

如果您希望我对上述内容进行详细说明，请告诉我。

- pbasdf

---

谢谢，我会尝试一下。虽然我从未使用过NSExpressions，但我知道它们是什么。 - nemesys

Answer 4

如果你想从表中删除重复项，可以执行以下查询：

delete from SomeTable where rowid not in ( select max(rowid) from SomeTable group by property1, property2, property3 )

- Parvesh Soni

Answer 5

使用以下查询语句从该表中删除重复数据：

delete from SomeTable where someID not in (select Min(someID) from SomeTable group by property1+property2+property3)

- Dhanasekar Marichamy

Answer 6

按组求最大值：

select t1.someId
      from SomeTable t1
        left outer join SomeTable t2
          on    t1.property1 = t2.property1
            and t1.property2 = t2.property2
            and t1.property3 = t2.property3
            and t1.someId < t2.someId
      where t2.someId  is null;

所以，这可能是答案

delete SomeTable 
where someId not in
 (select t1.someId
  from SomeTable t1
    left outer join SomeTable t2
      on    t1.property1 = t2.property1
        and t1.property2 = t2.property2
        and t1.property3 = t2.property3
        and t1.someId < t2.someId
  where t2.someId  is null); 

Sqlfiddle演示

- Mohsen Heydari

Answer 7

你可以使用exists函数来检查每一行是否存在另一行，其ID与当前行不相等，并且所有定义行的重复标准的其他属性与当前行的所有属性相等。

delete from something 
where
    id in (SELECT 
        sm.id
    FROM
        sometable sm

    where
        exists( select 
            1
        from
            sometable sm2

        where
            sm.prop1 = sm2.prop1
            and sm.prop2 = sm2.prop2
            and sm.prop3 = sm2.prop3
            and sm.id != sm2.id)
      );

- chebus

Answer 8

我认为你可以通过创建一个派生的duplicate_flg列并在三个属性值相等时将其设置为1来轻松处理这个问题。完成后，您只需删除那些duplicate_flg = 1的记录即可。以下是如何执行此操作的示例查询：

--retrieve all records that has same property values (property1,property2 and property3) 
SELECT *
FROM (
    SELECT someid
        ,property1
        ,property2
        ,property3
        ,CASE 
            WHEN property1 = property2
                AND property1 = property3
                THEN 1
            ELSE 0
            END AS duplicate_flg
    FROM SomeTable
    ) q1
WHERE q1.duplicate_flg = 1;

这是一个关于编程的内容，翻译如下：

这里是一个示例delete语句：

DELETE
FROM something
WHERE someid IN (
        SELECT someid
        FROM (
            SELECT someid
                ,property1
                ,property2
                ,property3
                ,CASE 
                    WHEN property1 = property2
                        AND property1 = property3
                        THEN 1
                    ELSE 0
                    END AS duplicate_flg
            FROM SomeTable
            ) q1
        WHERE q1.duplicate_flg = 1
        );

- FutbolFan

Answer 9

如果您想删除所有重复记录，请尝试以下代码：

WITH tblTemp  as
(
    SELECT ROW_NUMBER() Over(PARTITION BY Property1,Property2,Property3 ORDER BY Property1) As RowNumber,* FROM Table_1
)
DELETE FROM tblTemp where RowNumber >1

希望这能帮到你。

- Developer Nation