求SQL Server查询语句：获取指定节点的所有直接/间接关联嵌套数据

测试数据定义 #

declare @tmpData as table
(
MainId int,
RefId int
)

INSERT INTO @tmpData
            (MainId,
             RefId)
VALUES      (1, NULL),
            (2, 1),
            (3, 2),
            (4, 3),
            (5, NULL),
            (6, 5); 

需求说明 #

传入一个指定值（例如1），查询出所有与该值直接或间接关联的行。以传入1为例，需返回MainId为1、2、3、4的行（5、6与1无关联，不返回）。

解决方案：使用递归CTE #

普通自连接只能处理固定层级的关联，无法应对不确定深度的间接关联，这里可以用SQL Server的递归公共表表达式（CTE）实现：

-- 定义传入的指定值
declare @InputValue int = 1;

with RecursiveData as (
    -- 锚点查询：定位初始目标行
    select MainId, RefId
    from @tmpData
    where MainId = @InputValue
    union all
    -- 递归查询：遍历所有间接关联行
    select t.MainId, t.RefId
    from @tmpData t
    inner join RecursiveData rd on t.RefId = rd.MainId
)
select * from RecursiveData;

逻辑说明 #

1. 锚点部分：先找到传入值对应的初始行（MainId等于指定值）。
2. 递归部分：通过自连接持续查找RefId等于上一层级MainId的行，遍历所有直接或间接关联的节点。
3. 最终返回递归CTE的所有数据，即为所有关联行。

执行上述SQL，当@InputValue=1时，会返回MainId为1、2、3、4的四行数据，符合需求。

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