如何编写SQL查询获取记录的最新及前两条历史评论？

表结构 #

Record表

Comment表

需求 #

获取每条记录的最新评论，以及往前追溯的两条历史评论，结果格式如下：

解决方案 #

你之前的递归CTE方向有误，应该从每条记录的最新评论开始往回追溯历史评论，同时记录层级来区分不同的评论位置。以下是修正后的SQL：

WITH CommentHierarchy AS (
    -- 起始部分：获取每条记录的最新评论，层级标记为0
    SELECT
        r.RecordName,
        c.Id AS CommentId,
        c.Comment,
        c.PreviousCommentId,
        0 AS Level
    FROM Record r
    JOIN Comment c ON r.LatestCommentId = c.Id

    UNION ALL

    -- 递归部分：往回追溯上一条评论，层级递增，最多追溯2层
    SELECT
        ch.RecordName,
        c.Id AS CommentId,
        c.Comment,
        c.PreviousCommentId,
        ch.Level + 1 AS Level
    FROM CommentHierarchy ch
    JOIN Comment c ON ch.PreviousCommentId = c.Id
    WHERE ch.Level < 2 -- 只需要前两条历史评论，所以层级到2为止
)
-- 用条件聚合将不同层级的评论转为列
SELECT
    RecordName,
    MAX(CASE WHEN Level = 0 THEN Comment END) AS LatestComment,
    MAX(CASE WHEN Level = 1 THEN Comment END) AS PreviousComment,
    MAX(CASE WHEN Level = 2 THEN Comment END) AS PreviousComment1
FROM CommentHierarchy
GROUP BY RecordName
ORDER BY RecordName;

代码说明 #

1. 递归CTE起始段：通过Record表的LatestCommentId关联Comment表，拿到每条记录的最新评论，同时标记层级为0（代表最新）。
2. 递归段：基于上一层的PreviousCommentId关联到历史评论，层级加1，并且通过Level < 2限制只追溯两层历史，避免不必要的递归。
3. 条件聚合：对每个记录按RecordName分组，用CASE语句将不同层级的评论映射到对应的列中，用MAX确保每个层级只取一条评论（因为每个层级对应唯一的历史评论）。

原代码问题分析 #

你之前的CTE是从最早的评论（PreviousCommentId IS NULL）开始正向递归，这和需求的“从最新评论往回找历史”方向相反，而且没有关联Record表，无法对应到具体的记录，也没有层级标记来区分评论的先后顺序，所以无法得到目标结果。

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