--生成测试数据
create table ddTabale(ID int,ParentID int,msg varchar(20))
insert into ddTabale select 1,0,null
insert into ddTabale select 2,1,null
insert into ddTabale select 3,1,null
insert into ddTabale select 4,2,null
insert into ddTabale select 5,3,null
insert into ddTabale select 6,5,null
insert into ddTabale select 7,6,null
go
Declare @Id Int
Set @Id = 1; ---在此修改父节点
With ddCTE(Id,ParentId,msg)
As
(
Select Id,ParentId,msg From ddTabale Where ParentId In (@Id)
Union All
Select ddTabale.Id,ddTabale.ParentId,ddTabale.msg From ddCTE
Inner Join ddTabale
On ddCTE.Id = ddTabale.ParentId
)
Select * From ddCTE