如果字段“post_title”是如何删除表行的，被重复在另一行？ [英] How to delete a table row if the field "post_title" is duplicated in another row?

问题描述

可能重复：

我在我的数据库中有一个 posts_table
这个posts_table有字段 post_title ， post_id ，（和其他一些不重要的）。

I have a posts_table in my DB.. That posts_table have the fields post_title, post_id, (and some others that dont matter).

如果post_title重复，我需要删除一行另一行。

I need to delete one of the rows if the post_title repeats in another row.

示例：

posts_table
-------------------------------------------
post_id  | post_title
-------------------------------------------
501      |  Some post title here  
502      |  Another post title
503      |  A test post tile
504      |  Some post title here  (this is duplicated, i need to delete this row)
505      |  A different post title

使用下面的句子，我可以检查所有重复的post_titles

With the sentence below i can check all duplicated post_titles

SELECT    post_title, COUNT(post_title) AS dup_count
FROM      posts_table
GROUP BY  post_title
HAVING    (COUNT(post_title) > 1) 

问题是，我如何删除所有的行与重复的post_titles？

The question is, How can i delete all rows with duplicated post_titles??

最好和最快的查询是：

delete from posts_table where post_id in (
  select post_id from (
    select post_id from posts_table a group by post_title having count(post_title) > 1
  ) b
)

推荐答案

尝试这一个：

DELETE FROM Posts_Table 
WHERE Post_ID NOT IN    
    (
     SELECT a.Post_ID
     FROM
        (
         SELECT Post_Title, Post_ID 
         FROM Posts_Table 
         GROUP BY Post_Title
         ) a
    )

这篇关于如果字段“post_title”是如何删除表行的，被重复在另一行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！