MySQL实现帖子评论按层级从下到上查询的SQL问题

嘿，我完全懂你的需求——你想要把某个主帖下的评论从最底层的回复往顶层主帖倒序排列，而不是常规的主帖→一级评论→二级评论这种从上到下的顺序。你的现有查询只靠comment_of和id_post排序，肯定达不到这个效果，因为这是按父节点和节点ID的正序来排的，得用递归查询来处理这种树形结构的层级排序。

核心思路 #

我们需要先给每个评论节点标记深度层级（主帖深度为0，一级评论深度1，二级深度2，以此类推），然后按深度从大到小排序，这样最底层的评论（深度最大）就会排在最前面，逐步往上到主帖。

适用MySQL 8+/PostgreSQL的递归CTE方案 #

假设你要查询主帖id_post=25下的所有评论，用递归CTE（公共表表达式）可以轻松实现：

WITH RECURSIVE comment_tree AS (
    -- 锚点：先定位目标主帖
    SELECT 
        id_post, 
        post, 
        comment_of,
        0 AS depth,  -- 主帖深度设为0
        CAST(id_post AS CHAR(255)) AS path  -- 记录节点路径，方便后续扩展
    FROM posts
    WHERE id_post = 25  -- 替换成你要查询的主帖ID
    UNION ALL
    -- 递归：逐层查找子评论
    SELECT 
        p.id_post, 
        p.post, 
        p.comment_of,
        ct.depth + 1 AS depth,  -- 子节点深度比父节点+1
        CONCAT(ct.path, '→', p.id_post) AS path
    FROM posts p
    JOIN comment_tree ct ON p.comment_of = ct.id_post
)
-- 按深度降序排列，实现从底层到顶层的顺序
SELECT id_post, post, comment_of
FROM comment_tree
ORDER BY depth DESC;

效果验证 #

用你给出的数据示例，这个查询会返回：

完美符合你要的从最底层到顶层的排序需求。

扩展：查询所有主帖及其评论 #

如果需要查询所有主帖（comment_of=0的节点），且每个主帖下的评论都按底层到顶层排序，可以调整锚点和排序规则：

WITH RECURSIVE comment_tree AS (
    SELECT 
        id_post, 
        post, 
        comment_of,
        0 AS depth,
        CAST(id_post AS CHAR(255)) AS path
    FROM posts
    WHERE comment_of = 0  -- 选中所有主帖
    UNION ALL
    SELECT 
        p.id_post, 
        p.post, 
        p.comment_of,
        ct.depth + 1 AS depth,
        CONCAT(ct.path, '→', p.id_post) AS path
    FROM posts p
    JOIN comment_tree ct ON p.comment_of = ct.id_post
)
-- 先按主帖分组，再按深度降序
SELECT id_post, post, comment_of
FROM comment_tree
ORDER BY SUBSTRING_INDEX(path, '→', 1), depth DESC;

为什么你的原查询不行？ #

你原来的ORDER BY comment_of, id_post是按父节点ID和自身ID正序排列，会先显示主帖（comment_of=0），然后是所有父节点为25的一级评论，接着是父节点为26的二级评论……这是从上到下的层级顺序，和你要的方向正好相反。

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