WordPress站点MySQL查询：筛选特定重复评论并附加关联条件

你之前用AND同时连接两个LIKE条件无效，是因为单条评论的comment_content不可能同时以两个不同的字符串开头，必须通过关联同一comment_post_ID下的其他评论来实现筛选。

方法一：使用 EXISTS 子查询 #

SELECT c.*
FROM wpvo_comments c
-- 筛选出目标开头的评论
WHERE c.comment_content LIKE 'Dintero Order created with%'
-- 确保同一文章下存在另一种开头的评论
AND EXISTS (
    SELECT 1
    FROM wpvo_comments c2
    WHERE c2.comment_post_ID = c.comment_post_ID
    AND c2.comment_content LIKE 'The Dintero order%'
)
-- 确保该文章下目标开头的评论重复次数大于1
AND c.comment_post_ID IN (
    SELECT comment_post_ID
    FROM wpvo_comments
    WHERE comment_content LIKE 'Dintero Order created with%'
    GROUP BY comment_post_ID
    HAVING COUNT(*) > 1
);

方法二：使用 JOIN 关联查询 #

SELECT c.*
FROM wpvo_comments c
-- 先筛选出重复次数大于1的文章ID
INNER JOIN (
    SELECT comment_post_ID
    FROM wpvo_comments
    WHERE comment_content LIKE 'Dintero Order created with%'
    GROUP BY comment_post_ID
    HAVING COUNT(*) > 1
) AS dup_posts ON c.comment_post_ID = dup_posts.comment_post_ID
-- 关联同一文章下的另一种评论
INNER JOIN wpvo_comments c2 ON c.comment_post_ID = c2.comment_post_ID
WHERE c.comment_content LIKE 'Dintero Order created with%'
AND c2.comment_content LIKE 'The Dintero order%'
-- 去重，避免同一评论被多次返回
GROUP BY c.comment_ID;

这两种写法都能满足你的需求：先定位到以Dintero Order created with%开头的评论，同时验证同一文章ID下存在The Dintero order%开头的评论，并且该文章ID对应的目标评论数量大于1。

内容的提问来源于stack exchange，提问作者CyberTron