mysql - SQL 查询？ SELECT 这个查询的逆？

Question

我正在尝试使用 SQL 查找 wordpress 帖子的重复项 - 但根据重复的帖子元进行重复 - 而不是重复的标题。到目前为止，我能找到的最接近的代码恰恰相反——它找到了所有独特的帖子。我怎样才能撤销这个查询？

SELECT id,meta_value, post_title, post_content
FROM wp_posts
LEFT JOIN wp_postmeta c ON ( wp_posts.ID = c.post_id )
WHERE post_type = 'post' AND meta_key = 'syndication_permalink' 
GROUP BY meta_value
HAVING Count(meta_value) > 1

*UPDATE 很抱歉成为 SQL 菜鸟。我添加了一个表格来准确显示目标是什么。我想从 freelancer.com 删除重复的帖子

  post_id   meta_key                meta_value
  --------  -------------------     ----------------------------------------
  1         syndication_permalink   https://www.freelancer.com/projects/
  2         syndication_permalink   https://www.freelancer.com/projects/
  3         syndication_permalink   https://www.freelancer.com/projects/
  4         syndication_permalink   https://www.simplyhired.com/job/W6sVJ1
  5         syndication_permalink   https://www.mandy.com/uk/job/576913/junior

Answer 1

我不确定，您想要什么作为输出。但是试试这个代码，让我知道它是否解决了你的问题。

SELECT x.*
FROM wp_posts x
JOIN
(SELECT wp.meta_value
FROM wp_posts wp
LEFT JOIN wp_postmeta c ON ( wp.ID = c.post_id )
WHERE post_type = 'post' AND meta_key = 'syndication_permalink' 
GROUP BY wp.meta_value
HAVING Count(wp.meta_value) > 1) as y ON y.meta_value = x.meta_value

这将根据列 meta_value 给出所有重复值。如果我错了，请让我更正。