请教 Sql Server 用递归取得上一级的数据.

[disneyzhang]

Tree表如下:
NodeId1 NodeId2 ParentId1 ParentId2
a001 abc b001 abc

b001 abc c001 abc
b001 abc d001 abc

d001 abc f001 abc
d001 abc g001 abc
d001 abc h001 abc



已知: NodeId1 NodeId2 ,请问怎样得到它的上一级数据呢?

例如 NodeId1=a001 NodeId2=abc

结果
a001
b001
c001
d001
f001
g001
h001

[$扫地僧$]

create table A
(
Nodeid1 varchar(5),
Nodeid2 varchar(5),
ParentId1 varchar(5),
parentId2 varchar(5)
)

insert A select 'a001','abc','b001','abc'
insert A select 'b001','abc','c001','abc'
insert A select 'b001','abc','d001','abc'
insert A select 'd001','abc','f001','abc'
insert A select 'd001','abc','g001','abc'
insert A select 'd001','abc','h001','abc'


declare @T table(Nodeid1 varchar(5),ParentId1 varchar(5),lev int)
declare @Nodeid1 varchar(5)
declare @Nodeid2 varchar(5)
declare @lev int
set @lev=1
set @Nodeid1='a001'
set @Nodeid2='abc'
insert @T select Nodeid1,ParentId1,@lev from A where Nodeid1=@Nodeid1 and Nodeid2=@Nodeid2
while @@rowcount>0
begin
set @lev=@lev+1
insert @T select Nodeid1,ParentId1,@lev from A where Nodeid1 in (select ParentId1 from @T where lev=@lev-1)
end
select ParentId1 from @T

[$扫地僧$]

create proc T_proc(@Nodeid1 varchar(5),@Nodeid2 varchar(5))
as
declare @T table(Nodeid1 varchar(5),ParentId1 varchar(5),lev int)
declare @lev int
set @lev=1
insert @T select Nodeid1,ParentId1,@lev from A where Nodeid1=@Nodeid1 and Nodeid2=@Nodeid2
while @@rowcount>0
begin
set @lev=@lev+1
insert @T select Nodeid1,ParentId1,@lev from A where Nodeid1 in (select ParentId1 from @T where lev=@lev-1)
end
select ParentId1 from @T


exec T_proc 'a001','abc'

[$扫地僧$]

看漏了一点;
create proc T_proc(@Nodeid1 varchar(5),@Nodeid2 varchar(5))
as
declare @T table(ParentId1 varchar(5),lev int)
declare @lev int
set @lev=1
insert @T select Nodeid1,0 from A where Nodeid1=@Nodeid1 and Nodeid2=@Nodeid2
insert @T select ParentId1,@lev from A where Nodeid1=@Nodeid1 and Nodeid2=@Nodeid2
while @@rowcount>0
begin
set @lev=@lev+1
insert @T select ParentId1,@lev from A where Nodeid1 in (select ParentId1 from @T where lev=@lev-1)
end
select ParentId1 from @T


exec T_proc 'a001','abc'

[子陌红尘]

--生成测试数据
create table BOM(NodeId1 varchar(10),NodeId2 varchar(10),ParentId1 varchar(10),ParentId2 varchar(10))
insert into BOM select 'a001','abc','b001','abc'
insert into BOM select 'b001','abc','c001','abc'
insert into BOM select 'b001','abc','d001','abc'
insert into BOM select 'd001','abc','f001','abc'
insert into BOM select 'd001','abc','g001','abc'
insert into BOM select 'd001','abc','h001','abc'
go

--创建用户定义函数
create function f_getParent(@NodeId1 VARCHAR(10),@NodeId2 VARCHAR(10))
returns @t table(NodeId1 varchar(10),NodeId2 varchar(10),ParentId1 varchar(10),ParentId2 varchar(10))
as
begin
insert into @t select * from BOM where NodeId1=@NodeId1 and NodeId2=@NodeId2
while @@rowcount<>0
begin
insert into @t
select
a.*
from
BOM a,@t b
where
a.NodeId1=b.ParentId1 and a.NodeId2=b.ParentId2
and
not exists(select 1 from @t where NodeId1=a.NodeId1 and NodeId2=a.NodeId2)
end
return
end
go

--执行查询
select ParentId1 from dbo.f_getParent('a001','abc')
go

--输出结果
/*
ParentId1
----------
b001
c001
d001
f001
g001
h001
*/

--删除测试数据
drop function f_getParent
drop table BOM

[disneyzhang]

请问 libin_ftsafe(子陌红尘) ,上面的有什么办法可以使查询的速度快一些.

因为我现在用的 BOM table 数据是一个view 数据来的,并不全都是在一个table里面,

而且数据量比较大,有多重循环,现在查询时通常都需要3分钟的时间.

[$扫地僧$]

create function f_getParent(@NodeId1 VARCHAR(10),@NodeId2 VARCHAR(10))
returns @T table (ParentId1 varchar(5),lev int)
as
begin
declare @lev int
set @lev=1
insert @T select Nodeid1,0 from A where Nodeid1=@Nodeid1 and Nodeid2=@Nodeid2
insert @T select ParentId1,@lev from A where Nodeid1=@Nodeid1 and Nodeid2=@Nodeid2
while @@rowcount>0
begin
set @lev=@lev+1
insert @T select T.ParentId1,@lev from A T where exists (select 1 from @T where lev=@lev-1 and ParentId1=T.Nodeid1)
end
return
end



select ParentId1 from dbo.f_getParent('a001','abc')

[ReViSion]

用这个试试，看会不会快点，借用两位老大的，改了点
--生成测试数据
create table BOM(NodeId1 varchar(10),NodeId2 varchar(10),ParentId1 varchar(10),ParentId2 varchar(10))
insert into BOM select 'a001','abc','b001','abc'
insert into BOM select 'b001','abc','c001','abc'
insert into BOM select 'b001','abc','d001','abc'
insert into BOM select 'd001','abc','f001','abc'
insert into BOM select 'd001','abc','g001','abc'
insert into BOM select 'd001','abc','h001','abc'
go

--创建用户定义函数
create function f_getParent(@NodeId1 VARCHAR(10),@NodeId2 VARCHAR(10))
returns @t table(NodeId1 varchar(10),NodeId2 varchar(10),ParentId1 varchar(10),ParentId2 varchar(10),dep int)
as
begin
Declare @dep int
select @dep=1

insert into @t select NodeId1,NodeId2,ParentId1,ParentId2,@dep from BOM where NodeId1=@NodeId1 and NodeId2=@NodeId2
while @@rowcount<>0
begin
set @dep=@dep+1

insert into @t
select
a.NodeId1,a.NodeId2,a.ParentId1,a.ParentId2,@dep
from
BOM a,@t b
where
b.dep=@dep-1 and a.NodeId1=b.ParentId1 and a.NodeId2=b.ParentId2
end
return
end
go

--执行查询
select parentid1 from dbo.f_getParent('a001','abc')
go

[disneyzhang]

想了几天,问题还是没有解决,继续请教上面的两位老大,

现在主要的问题是我调用的 BOM(A) 是一个Vew的,但是该view里面有50万多条数据,并且大

部分都是重复,如果先将BOM view用Group by时,打开BOM View非常慢, 测试过如果用Table的话,

速度将快很多,但是现在又不是将所有数据存在一个table中. 请问有什么办法解决这个问题呢?