一个类似递归的问题，请帮忙看看

Question

参考：
http://community.csdn.net/Expert/topic/3756/3756875.xml?temp=.6423456

Answer 1

// 以下是我写的，结果也不对CREATE PROCEDURE [dbo].[sp_GetAssociatedByLcID] 
@LcID as char(20),
@level int=0
AS
set nocount onif @level=0
begin
create table #t(LcID char(20),level int)
insert #t select Lc2,@level from tbl_LcAssociation where Lc1 = @LcID
if @@rowcount>0
begin
set @level=@level+1
exec sp_GetAssociatedByLcID @LcID,@level
end
select a.*,b.*
from  tbl_LcInfo a, tbl_LcAssociation b
where a.LcID=b.Lc1
and exists(select * from #t where LcID=b.Lc1)
end
else
begin
insert #t select a.Lc1,@level
from tbl_LcAssociation a,#t b
where a.Lc1= b.LcID
and b.level=@level-1
if @@rowcount>0
begin
set @level=@level+1
exec sp_GetAssociatedByLcID @LcID,@level
end
end
GO

Answer 2

create table tbl_LcAssociation (
   AssociationID        int                  identity,
   Lc1                  varchar(30)          null,
   Lc2                  varchar(30)          null,
   AssociationType      char(2)              null,
   constraint PK_TBL_LCASSOCIATION primary key  (AssociationID)
)
insert into tbl_LcAssociation
select '001' , '002', 'a'  union all
select '001' , '003', 'b'  union all
select '002' , '006', 'd'  union all
select '003' , '005', 'a'  union all
select '007' , '010', 'a'  union all
select '010' , '012', 'c'
insert into tbl_LcAssociation     --注意,我这里加了一条记录,是001的父结点
select null,'001',nullcreate function dbo.getSubtreeInfo (@Lc1 varchar(30))
returns @treeinfo table
(
   Lc1                  varchar(30)          null,
   Lc2                  varchar(30)          null,
   AssociationType      char(2)              null,
   level int
)
as
begin
declare @level as int
select @level = 0
insert into @treeinfo
select Lc1,Lc2,AssociationType,@level
from tbl_LcAssociation 
where Lc2=@Lc1
    while @@rowcount>0
    begin
         set @level=@level+1
         insert into @treeinfo
         select e.Lc1,e.Lc2,e.AssociationType,@level
         from tbl_LcAssociation e join @treeinfo t
         on e.Lc1=t.Lc2 and t.level=@level-1
    end
return
end
select * from tbl_LcAssociation a where exists
              (select 1 from dbo.getSubtreeInfo('001')
                            where Lc1=a.Lc1 and Lc2=a.Lc2 and AssociationType=a.AssociationType
               and Lc2<>'001' )--------------------------------------------------------
1 001 002 a 
2 001 003 b 
3 002 006 d 
4 003 005 a 

Answer 3

to didoleo(冷月无声) ( ) insert into tbl_LcAssociation     --注意,我这里加了一条记录,是001的父结点
select null,'001',null
---------------------总不能加一条数据吧

Answer 4

--创建测试数据
create table tbl_LcAssociation (
   Lc1                  varchar(30)          null
   Lc2                  varchar(30)          null,
   AssociationType      char(2)              null
)insert into tbl_lcassociation select '001','002','a'
insert into tbl_lcassociation select '001','003','b'
insert into tbl_lcassociation select '002','006','d'
insert into tbl_lcassociation select '003','005','a'
insert into tbl_lcassociation select '007','010','a'
insert into tbl_lcassociation select '010','012','c'
--创建存储过程
create procedure sp_test(@LcID varchar(10))
AS
begin
  declare @i int
  set @i = 0
  
  select @LcID as LcID,Level = @i,0 as Type
  into #t 
  
  
  while exists(select 1 
               from 
                   tbl_LcAssociation a,#t b 
               where 
                   (a.Lc1 = b.LcID or a.Lc2 = b.LcID) and b.Level = @i
               and
                   (case when (a.Lc1 = b.LcID) then a.Lc2 
                        when (a.Lc2 = b.LcID) then a.Lc1 end) not in (select LcID from #t))
  begin
      insert into #t
      select
          case when (a.Lc1 = b.LcID) then a.Lc2 
               when (a.Lc2 = b.LcID) then a.Lc1 end,
          @i + 1,
          1
      from 
          tbl_LcAssociation a,#t b 
      where 
          (a.Lc1 = b.LcID or a.Lc2 = b.LcID) and b.Level = @i
          and
                   (case when (a.Lc1 = b.LcID) then a.Lc2 
                        when (a.Lc2 = b.LcID) then a.Lc1 end) not in (select LcID from #t)
      
      set @i = @i+1
  end
  
  select LcID from #t where type > 0 order by LcID
end--执行存储过程，执行结果自己看
exec sp_test '002'

Answer 5

--query function 
create function f_id(@LcID varchar(20))
returns @re table(LcID varchar(20),level int)
as
begin
declare @l int
set @l=0
insert @re select Lc2,@l from tbl_LcAssociation
where Lc1=@LcID
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.Lc2,@l 
from tbl_LcAssociation a,@re b
where a.Lc1=b.LcID and b.level=@l-1
end
return
end
go--loading
select a.* from tbl_LcInfo a,f_id(N'001')b where a.LcID=b.LcID

Answer 6

--因为你的表里没有标出001的父亲是谁啊,要么是他自己001要么是null,我选择了后者
--用006或005 那我的做法是再写一个函数
create function dbo.getManager
(@Lc2 as varchar(30))
returns varchar(30)
begin
   declare @Lc1 varchar(30)
   set @Lc1='001'
   select @Lc1=Lc1 from tbl_LcAssociation where Lc2=@Lc2 and Lc1 is not null
   if @@rowcount>0
   return dbo.getManager(@Lc1)      
   return @Lc2
end
go--根据006来查
declare @Lc1 varchar(30)
select @Lc1=dbo.getManager('006')
select * from tbl_LcAssociation a where exists
              (select 1 from dbo.getSubtreeInfo(@Lc1)
                            where Lc1=a.Lc1 and Lc2=a.Lc2 and AssociationType=a.AssociationType
               and Lc2<>@Lc1 )---------------------------------------
1 001 002 a 
2 001 003 b 
3 002 006 d 
4 003 005 a 

Answer 7

谢谢各位大牛的解答，我都会认真试的。 didoleo(冷月无声) 
--因为你的表里没有标出001的父亲是谁啊,要么是他自己001要么是null,我选择了后者这个问题，邹建解决了，另外请解释一下--loading
select a.* from tbl_LcInfo a,f_id(N'001')b where a.LcID=b.LcID中的 N'001' ，N是什么意思，谢谢。
还有一个问题就是查005，006的，实在不行只能写两个函数了，然后把它封装到一个sp里面，在里面做两次合并一下结果，不知道效率怎么样。

Answer 8

--N'001'表示查001,要查005,006或其他的，只需要改调用就行了--查005
select a.* from tbl_LcInfo a,f_id(N'005')b where a.LcID=b.LcID--查006
select a.* from tbl_LcInfo a,f_id(N'005')b where a.LcID=b.LcID

Answer 9

谢谢邹建。查001 的方法没有问题，因为001对应 Lc1这个字段，我说查005，006的意思是他们在Lc2这个字段，直接用这个函数查不到的，可能还要重新写一个，不知道调用两遍，然后合并结果，效率怎么样。

Answer 10

查001 的方法没有问题，因为001对应 Lc1这个字段，我说查005，006的意思是他们在Lc2这个字段，直接用这个函数查不到的，可能还要重新写一个，不知道调用两遍，然后合并结果，效率怎么样。
不存在这种问题吧？查005是指与它有关系的，难道你还要反推关系？按你上面的数据，查005,需要查询出什么数据来？

Answer 11

--加多一层循环就OK了
create function f_id(@LcID varchar(20))
returns @re table(LcID varchar(20),level int)
as
begin
declare @l int
set @l=0
insert @re select Lc2,@l from tbl_LcAssociation
where Lc1=@LcID
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.Lc2,@l 
from tbl_LcAssociation a,@re b
where a.Lc1=b.LcID and b.level=@l-1
end insert @re select Lc1,@l from tbl_LcAssociation
where Lc2=@LcID
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.Lc1,@l 
from tbl_LcAssociation a,@re b
where a.Lc2=b.LcID and b.level=@l-1
end

return
end
go

Answer 12

http://community.csdn.net/Expert/topic/3778/3778627.xml?temp=.2196161