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
--遞歸調用，不知道是否想要這樣