declare @T table( Id int primary key, Name nvarchar(255) not null, ParentId int)insert into @T values(1, 'TestName1', NULL),(2, 'TestName2', 1),(3, 'TestName3', 2),(4, 'TestName4', NULL),(5, 'TestName5', 1)declare @Id int = 1;with cte as( select T.* from @T as T where T.Id = @Id union all select T.* from @T as T inner join cte as C on T.ParentId = C.Id)select *from cte
结果
Id Name ParentId----------- -------------------- -----------1TestName1 NULL2TestName2 15TestName5 13TestName3 2
