主查询与子查询问题：子查询未返回预期结果

原查询的问题出在WHERE子句里的子查询逻辑完全错了：

main.comment_id in ( SELECT posts_comments.comment_id FROM posts_comments WHERE posts_comments.replay_comment_id = main.comment_id )

这个子查询是在找评论ID等于当前main行comment_id，同时replay_comment_id也等于该ID的记录——但子评论的replay_comment_id是父评论的ID，不可能等于自身的comment_id，所以这个子查询永远返回空集合，导致只有replay_comment_id IS NULL的主评论能被筛选出来。

你要的结果是保留两类评论：

• 主评论（replay_comment_id IS NULL）
• 属于当前帖子主评论的子评论（也就是子评论的replay_comment_id对应当前帖子里某条主评论的comment_id）

方法1：先拿当前帖子的主评论ID，再筛选 #

先查出当前帖子的所有主评论ID，然后筛选主评论，或者replay_comment_id在这个ID集合里的子评论，再加上统计回复数的逻辑：

SELECT
    main.comment_id,
    main.comment,
    main.timestamp,
    main.replay_comment_id,
    COUNT(replay.comment_id) AS replays
FROM posts_comments AS main
LEFT JOIN posts_comments AS replay ON replay.replay_comment_id = main.comment_id
WHERE
    main.post = "107" 
    AND (
        main.replay_comment_id IS NULL 
        OR main.replay_comment_id IN (
            SELECT comment_id FROM posts_comments WHERE post = "107" AND replay_comment_id IS NULL
        )
    )
GROUP BY main.comment_id
ORDER BY main.comment_id ASC;

方法2：用JOIN关联父评论做验证 #

通过JOIN关联父评论表，直接验证子评论的父评论是当前帖子的主评论：

SELECT
    main.comment_id,
    main.comment,
    main.timestamp,
    main.replay_comment_id,
    COUNT(replay.comment_id) AS replays
FROM posts_comments AS main
LEFT JOIN posts_comments AS replay ON replay.replay_comment_id = main.comment_id
-- 关联父评论，只保留父评论是当前帖子主评论的子评论
LEFT JOIN posts_comments AS parent ON parent.comment_id = main.replay_comment_id
WHERE
    main.post = "107" 
    AND (
        main.replay_comment_id IS NULL 
        OR (parent.post = "107" AND parent.replay_comment_id IS NULL)
    )
GROUP BY main.comment_id
ORDER BY main.comment_id ASC;

结果验证 #

跑上面任意一个查询，都会返回ID为19、20的主评论，以及ID为21、22的20号主评论的子评论，同时自动排除ID为23的无效子评论。

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