查找父结点！
Declare @a Table(
IDInt,
sNamevarchar(20),
ParetIDint)
Insert Into @a Select 1, 'a ', 0
Insert Into @a Select 2, 'a_a ', 1
Insert Into @a Select 3, 'a_b ', 1
Insert Into @a Select 4, 'a_a_a ', 2
Insert Into @a Select 5, 'a_b_b ', 3
Insert Into @a Select 6, 'a_a_a_a ', 4
Insert Into @a Select 7, 'a_a_a_b ', 4
Select * from @a
/*
ID sName ParetID
----------- -------------------- -----------
1 a 0
2 a_a 1
3 a_b 1
4 a_a_a 2
5 a_b_b 3
6 a_a_a_a 4
7 a_a_a_b 4
*/
要求一函数，
Select dbo.fGetParentName( 'a_a_a_b ', 1)
参数说明：
a_a_a_b ：为任何一个节点的 Name
1：为所需要的父层次
[解决办法]
--參考鄒老大的例子
ALTER function dbo.fn_bom_t( @child_part varchar(30))
returns @t_level table(part varchar(30),level int)
AS
begin
declare @level int
set @level=0
insert into @t_level select @child_part,@level
while @@rowcount> 0
begin
set @level=@level+1
insert into @t_level select a.parent_part,@level
from Test a,@t_level b
where a.child_part=b.part
and b.level=@level-1
end
return
end
[解决办法]
create Table a(
IDInt,
sNamevarchar(20),
ParetIDint)
Insert Into a Select 1, 'a ', 0
Insert Into a Select 2, 'a_a ', 1
Insert Into a Select 3, 'a_b ', 1
Insert Into a Select 4, 'a_a_a ', 2
Insert Into a Select 5, 'a_b_b ', 3
Insert Into a Select 6, 'a_a_a_a ', 4
Insert Into a Select 7, 'a_a_a_b ', 4
Select * from a
/*
ID sName ParetID
----------- -------------------- -----------
1 a 0
2 a_a 1
3 a_b 1
4 a_a_a 2
5 a_b_b 3
6 a_a_a_a 4
7 a_a_a_b 4
*/
/*要求一函数，
Select dbo.fGetParentName( 'a_a_a_b ', 1)
参数说明：
a_a_a_b ：为任何一个节点的 Name
1：为所需要的父层次*/
go
create function fGetParentName(@sName varchar(20), @layer int)
returns int as
begin
declare @ParentID int
select @ParentID=ParetID from a where sName=@sName
while @layer> 1
begin
select @ParentID=ParetID from a where ID=@ParentID
set @layer = @layer - 1
end
return @ParentID
end
go
select dbo.fGetParentName( 'a_a_a_b ', 1) union all
select dbo.fGetParentName( 'a_a_a_b ', 2) union all
select dbo.fGetParentName( 'a_a_a_b ', 3)
drop function fGetParentName
drop table a
