Azure SQL如何查询多层级响应并匹配对应根评论ID

表结构说明 #

• comments（根评论表）
  • commenid：根评论唯一标识
  • comment：根评论内容
• responses（回复表，支持多层嵌套）
  • Responseid：单条回复唯一标识
  • response：回复内容
  • linkid：关联字段：关联commenid时为根评论的首层直接回复；关联其他Responseid时为对应回复的嵌套下级回复
  • createddate：回复创建时间

多层嵌套的回复链路无法通过固定层数的JOIN实现根溯源，Azure Synapse专用SQL池（原Azure Data Warehouse）支持递归公用表表达式（CTE），可以沿着回复链路逐层向上遍历，直到匹配到根评论ID。

查询语句 #

WITH ResponseTrace AS (
    -- 锚点：查询所有直接挂载在根评论下的首层回复
    SELECT
        r.Responseid,
        r.response,
        r.linkid,
        CAST(r.linkid AS NVARCHAR(100)) AS RootCommentId -- 字段类型和commenid保持一致即可，避免递归时类型不匹配
    FROM responses r
    INNER JOIN comments c
        ON r.linkid = c.commenid

    UNION ALL

    -- 递归：逐层关联下一级嵌套回复，继承上一级记录的根评论ID
    SELECT
        child.Responseid,
        child.response,
        child.linkid,
        parent.RootCommentId
    FROM responses child
    INNER JOIN ResponseTrace parent
        ON child.linkid = parent.Responseid
)
-- 按要求输出四个字段，根评论ID字段别名设为comments
SELECT
    Responseid,
    response,
    linkid,
    RootCommentId AS comments
FROM ResponseTrace
-- 可根据业务实际最大嵌套层级调整递归深度限制，默认最大递归深度为100
OPTION (MAXRECURSION 200);

注意事项 #

• 若存在脏数据导致回复链路出现循环引用，MAXRECURSION参数会在达到指定深度时终止查询，避免长时间占用资源
• 若commenid和Responseid字段数据类型不一致，锚点部分RootCommentId的类型要和commenid保持一致，避免递归过程中出现类型转换错误

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