删除所有条件很少的重复主题

本文介绍了删除所有条件很少的重复主题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试让 sql 删除所有重复的标题但必须在以下条件下删除重复的标题:

I'm trying to make sql who will delete all duplicate titles BUT must delete duplicates with these conditions:

• 必须只删除具有相同 object_id 的重复项
• 必须只保留最新的记录(最大的topic_id)(topic_id 是每个主题 AI 的唯一 id)

• must delete only duplicates with same object_id
• must keep only the newest record (biggest topic_id) (topic_id is the unique id for every topic AI)

到目前为止，我已经完成了(使用 select... 进行测试)

So far I've done that (testing with select...)

SELECT topic_id,object_id,title,url,date FROM topics GROUP BY title HAVING ( COUNT(title) > 1) ORDER BY topic_id DESC

但不符合条件.我正在使用 mysql.

But doesn't meet the conditions. I'm using mysql.

推荐答案

在 MySQL 中，不能将目标表指定给子查询中的 DML 操作(除非您将其嵌套不止一层，但在这种情况下，您将无法获得可靠的结果，也无法使用相关的子查询).

In MySQL, you cannot specify the target table to a DML operation in a subquery (unless you nest it more than one level deep, but in this case you won't get reliable results and cannot use correlated subqueries).

使用 JOIN:

DELETE td FROM topics td JOIN topics ti ON ti.object_id = td.object_id AND ti.title = td.title AND ti.topic_id > td.topic_id;

在 topics (object_id, title, topic_id) 上创建索引，以便快速工作.

Create an index on topics (object_id, title, topic_id) for this to work fast.