SQL实现分层评论数据特定排序：主评论降序子评论升序

问题背景 #

现有包含parent_id、comment_id、comment_level字段的评论表数据：

需要实现以下排序规则：

• 主评论（comment_level=1）按comment_id降序排列；
• 子评论（comment_level≠1）按comment_id升序排列，且必须紧跟在对应的主评论之后。

解决方案 #

核心思路是给每条评论标记其所属的主评论标识，以此作为分组排序的第一关键字，再区分主/子评论的排序优先级。

假设评论表名为comments，且子评论的parent_id关联的是主评论的主键（id字段），可以用CTE（公共表表达式）实现：

WITH comment_with_root AS (
    SELECT 
        c.parent_id,
        c.comment_id,
        c.comment_level,
        -- 标记每条评论对应的主评论comment_id：主评论用自身的，子评论关联对应主评论的
        COALESCE(root.comment_id, c.comment_id) AS root_comment_id
    FROM comments c
    -- 左连接主评论，获取子评论对应的主评论标识
    LEFT JOIN comments root 
        ON c.parent_id = root.id 
        AND root.comment_level = 1
)
SELECT parent_id, comment_id, comment_level
FROM comment_with_root
ORDER BY 
    -- 第一优先级：按主评论comment_id降序，确保主评论组的整体顺序
    root_comment_id DESC,
    -- 第二优先级：主评论排在组内最前面，子评论靠后
    CASE WHEN comment_level = 1 THEN 0 ELSE 1 END,
    -- 第三优先级：子评论自身按comment_id升序排列
    comment_id ASC;

逻辑说明 #

1. CTE关联主评论：通过左连接，给子评论匹配到对应的主评论comment_id，主评论则直接使用自身的comment_id作为root_comment_id，这样同一主评论下的所有评论会拥有相同的root_comment_id。
2. 多层排序：
   • 第一层按root_comment_id降序，实现主评论组的整体降序排列；
   • 第二层用CASE表达式让主评论（level=1）排在组内首位；
   • 第三层让子评论按自身comment_id升序，满足子评论的排序要求。

为什么UNION方案不可行 #

UNION合并多个子查询后，排序是全局生效的，无法将子评论与对应的主评论绑定在同一组内，因此无法实现“子评论紧跟主评论”的需求。必须通过关联主评论标识的方式，将同组评论聚合后再排序。

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