如何在SQL Server中编写SQL查询返回名称变更的完整链式序列

实现可行性 #

SQL Server 完全支持该需求，可通过**递归公用表表达式（递归CTE）**实现层级链路的拼接查询，适配这种自引用的变更关联场景。

前置说明 #

假设你的变更记录表名为ChangeLog，包含两个字段：

• beforename：变更前值（对应父节点）
• aftername：变更后值（对应子节点）

测试数据准备 #

如果需要复现示例效果，可先执行以下语句创建测试表和插入测试数据：

-- 创建测试表
CREATE TABLE ChangeLog (
    beforename VARCHAR(50),
    aftername VARCHAR(50)
);

-- 插入示例数据
INSERT INTO ChangeLog (beforename, aftername)
VALUES ('a','b'), ('b','c'), ('c','d');

完整查询SQL #

WITH RecursiveChain AS (
    -- 锚点成员：定位所有链路的起始节点（没有作为过变更后值的变更前值就是链路起点）
    SELECT 
        beforename AS start_node,
        aftername AS current_end_node,
        CAST(CONCAT(beforename, ' -> ', aftername) AS VARCHAR(MAX)) AS change_chain
    FROM ChangeLog
    WHERE beforename NOT IN (SELECT aftername FROM ChangeLog)

    UNION ALL

    -- 递归成员：逐层级拼接后续变更节点
    SELECT 
        rc.start_node,
        cl.aftername AS current_end_node,
        CAST(CONCAT(rc.change_chain, ' -> ', cl.aftername) AS VARCHAR(MAX)) AS change_chain
    FROM RecursiveChain rc
    INNER JOIN ChangeLog cl ON rc.current_end_node = cl.beforename
)
-- 筛选出已经到链路终点的完整结果（没有后续变更的节点就是链路终点）
SELECT change_chain 
FROM RecursiveChain
WHERE current_end_node NOT IN (SELECT beforename FROM ChangeLog);

结果说明 #

针对示例数据执行上述查询后，返回结果为：a -> b -> c -> d，和预期完全一致。

注意事项 #

• 如果存在多条互不关联的独立变更链路，该查询会一次性返回所有链路的完整结果
• 如果变更链路层级超过SQL Server默认的100层递归限制，可在查询语句末尾添加OPTION (MAXRECURSION 0)解除限制（0代表无递归层级上限，也可按需设置为具体数值，最大值为32767）
• 字符串显式转换为VARCHAR(MAX)是为了避免链路过长时字符串拼接溢出，可根据实际业务调整字段类型

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