请教关于树形查询的问题。
有两张表：
-----------------------
表Categories
Cate_Id       int
Name             nvarchar(50)
-----------------------
表Categories_Relations
Parent_Id   int
Child_Id     int
-----------------------
根据表Categories中的一个Cate_Id值，关联表Categories_Relations，如何查出此数值的所有子类？
如何查出此数值的所有父类？

------解决方案--------------------


select Categories.Cate_Id,Categories_Relations.Parent_Id
from Categories join Categories_Relations on Categories.Cate_Id=Categories_Relations.Child_Id
order by Cate_Id
------解决方案--------------------
自己写个函数...
------解决方案--------------------
--建立測試環境
Create Table Categories
(Cate_Id int,
Name nvarchar(50))
Create Table Categories_Relations
(Parent_Id int,
Child_Id int)
Insert Categories Select 1, 'A1 '
Union All Select 2, 'A2 '
Union All Select 3, 'A3 '
Union All Select 5, 'A5 '
Union All Select 6, 'A6 '
Union All Select 7, 'A7 '

Insert Categories_Relations Select 1, 2
Union All Select 1, 3
Union All Select 3, 5
Union All Select 5, 6
Union All Select 6, 7
GO
--建立函數
--得到所有的子節點
Create Function GetChildren(@Cate_Id Int)
Returns @Tree Table (Child_Id Int)
As
Begin
Insert @Tree Select Child_Id From Categories_Relations Where Parent_Id = @Cate_Id
While @@Rowcount > 0
Insert @Tree Select A.Child_Id From Categories_Relations A Inner Join @Tree B On A.Parent_Id = B.Child_Id And A.Child_Id Not In (Select Child_Id From @Tree)
Return
End
GO
--得到所有的父節點
Create Function GetParent(@Cate_Id Int)
Returns @Tree Table (Parent_Id Int)
As
Begin
Insert @Tree Select Parent_Id From Categories_Relations Where Child_Id = @Cate_Id
While @@Rowcount > 0
Insert @Tree Select A.Parent_Id From Categories_Relations A Inner Join @Tree B On A.Child_Id = B.Parent_Id And A.Parent_Id Not In (Select Parent_Id From @Tree)
Return
End
GO
--測試
Select * From dbo.GetChildren(5)
Select * From dbo.GetParent(5)
GO
--刪除測試環境
Drop Table Categories, Categories_Relations
Drop Function GetParent, GetChildren
--結果
/*
Child_Id
6
7

Parent_Id
3
1
*/
------解决方案--------------------
--举例
create table shu (id int ,--id
pid int) --父节点id
insert into shu
select 1,0 union all
select 2,1 union all
select 3,2 union all
select 4,0 union all
select 5,4

go
/*-- 得到指定id的子id列表 --*/
create function f_getchildid(@id int)
returns @re table(id int)
as
begin
insert into @re select @id
while @@rowcount> 0
insert into @re select a.id
from shu a inner join @re b on a.pid=b.id
where a.id not in(select id from @re)
return
end
go

--调用函数实现查询
select a.*
from shu a join dbo.f_getchildid(1) b on a.id=b.id
go
drop table shu
drop function f_getchildid
------解决方案--------------------
将你的表结构套用即可