如何用视图或者函数实现类似循环的功能

[dj0109]

我有这样的一个问题：在一个表里面我有很多单位，单位有上级单位，可能上级单位还有上级单位，单位层次部确定，在每个单位里面有一个字段par_id是保存其上级单位的，现在我怎么样能够将其中的一部分（或者某一个）单位的所有上级全部取出来？望高手赐教

[子陌红尘]

--生成测试数据
create table BOM(Par_ID INT,ID INT)
insert into BOM select null,1
insert into BOM select 1,2
insert into BOM select 2,3
insert into BOM select 3,4
insert into BOM select 4,5
insert into BOM select 5,6
insert into BOM select 6,7
go

--创建用户定义函数
create function f_getparent(@ID INT)
returns @t table(ID INT,Level INT)
as
begin
declare @i int
set @i = 0
insert into @t select @ID,@i

while @@rowcount<>0
begin
set @i = @i + 1

insert into
@t
select
a.Par_ID,@i
from
BOM a,@t b
where
a.ID=b.ID
and
b.Level = @i-1
and
a.Par_ID is not null
end

return
end
go

--执行查询
select ID from dbo.f_getparent(5) order by Level desc
go

[子陌红尘]

--生成测试数据
create table BOM(Par_ID INT,ID INT)
insert into BOM select null,1
insert into BOM select 1,2
insert into BOM select 2,3
insert into BOM select 3,4
insert into BOM select 4,5
insert into BOM select 5,6
insert into BOM select 6,7
go

--创建用户定义函数
create function f_getparent(@ID INT)
returns @t table(ID INT,Level INT)
as
begin
declare @i int
set @i = 0
insert into @t select @ID,@i

while @@rowcount<>0
begin
set @i = @i + 1

insert into
@t
select
a.Par_ID,@i
from
BOM a,@t b
where
a.ID=b.ID
and
b.Level = @i-1
and
a.Par_ID is not null
end

return
end
go

--执行查询
select ID from dbo.f_getparent(5) order by Level desc
go

[welch2008]

这本质上是一个递规问题，可以参考递规的写法写一个递规函数