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

Question

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

Answer 1

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'

Answer 2

看漏了一点;
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'

Answer 3

--生成测试数据
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

Answer 4

请问 libin_ftsafe(子陌红尘) ,上面的有什么办法可以使查询的速度快一些.因为我现在用的 BOM table 数据是一个view 数据来的,并不全都是在一个table里面, 而且数据量比较大,有多重循环,现在查询时通常都需要3分钟的时间.

Answer 5

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 
endselect ParentId1 from dbo.f_getParent('a001','abc')

Answer 6

想了几天,问题还是没有解决,继续请教上面的两位老大,现在主要的问题是我调用的 BOM(A) 是一个Vew的,但是该view里面有50万多条数据,并且大部分都是重复,如果先将BOM view用Group by时,打开BOM View非常慢, 测试过如果用Table的话,速度将快很多,但是现在又不是将所有数据存在一个table中. 请问有什么办法解决这个问题呢?

Answer 7

用这个试试，看会不会快点，借用两位老大的，改了点
--生成测试数据
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