sql查询父节点所有子节点

Ⅰ sql根据子节点查出所有的父节点的

用函数做，根据你的表结构改：
父节点查询子节点
create function GetChildID(@ParentID int)
returns @t table(ID int)
as
begin
insert into @t select id from table where parent_id = @ParentID
while @@rowcount<>0
begin
insert into @t select a.id from table as a
inner join @t as b
on a.parent_id = b.ID
and not exists(select 1 from @t where ID=a.ID)
end
return
end
go

子节点查询父节点
create function GetParentID(@ChildID int)
returns @t table(PID int)
as
begin
insert into @t select parent_id from table where ID=@ChildID
while @@rowcount<>0
begin
insert into @t select a.parent_id from table as a
inner join @t as b
on a.ID=b.PID
and not exists(select 1 from @t where PID=a.parent_id)
end
return
end
go

Ⅱ SQL语句查询出父节点下的所有子节点

createtable##tmp_users(idint,usernamenvarchar(255),parentidint)
declare@IDint
select@ID=idfromt_Userstwhereexists
(select*fromt_Userst2wheret2.id=t.parentidandt2.username='user1')
execAddSons@ID
select*from##tmp_users
droptable##tmp_users


--存储
createprocereAddSons@idint
as
ifexists(select*fromt_Userswhereparentid=@id)
begin
declare@tmp_IDint
declarecurcursorfor
selectidfromt_Userswhereparentid=@id
opencur
fetchnextfromcurinto@tmp_ID
while@@FETCH_STATUS=0
begin
insertinto##tmp_users
select*fromt_Userstwhereid=@tmp_ID
ifexists(select*fromt_Userswhereparentid=@tmp_ID)
begin
execAddSons@tmp_ID
end
fetchnextfromcurinto@tmp_ID
end
closecur
DEALLOCATEcur
end
--递归调用，不知道是否想要这样