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

[minajo21]

有两张表：主表和关系表

以下是表结构:

-- 主表信息
create table tbl_LcInfo (
LcID varchar(20) not null,
DealTime datetime null,
...
constraint PK_TBL_LCINFO primary key (LcID)
)
go

-- 关系表
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)
)

其中关系表的Lc1/Lc2字段，对应主表的LcID字段

比如有如下的纪录：

Lc1 Lc2 Type
001 002 a
001 003 b
002 006 d
003 005 a
007 010 a
010 012 c
...

001和002，003有直接的关系，
而002和006，003和005也有直接关系,
所以 001,002,003,005,006 是一组关系

同样 004,010,012 也是一组关系。


问题：
如何根据 001 查到 002,003,005,006 ? ?
或者 根据006 也能查到 001,002,003,005 ? ?

[子陌红尘]

--创建测试数据
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'

[zjcxc]

--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

[didoleo]

--因为你的表里没有标出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

[minajo21]

谢谢各位大牛的解答，我都会认真试的。



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里面，在里面做两次合并一下结果，不知道效率怎么样。

[zjcxc]

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


不存在这种问题吧？查005是指与它有关系的，难道你还要反推关系？按你上面的数据，查005,需要查询出什么数据来？

[zjcxc]

查002是否要把001和005都查询出来？

[minajo21]

是我没说清楚

001,002,003,005,006 是一组关系，要求用这组里面的任意一个，都要查出其他的来...

[minajo21]

实在是麻烦啊~~

[zjcxc]

查002是否要把001和005都查询出来？

[minajo21]

要的

[zjcxc]

--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

[zjcxc]

如果不查中文的话
不需要加N，加是防止查中文时出现乱码（与系统环境有关）

[minajo21]

谢谢邹建。

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

[lxysjl]

我顶

[lemon_lyk]

学习

[minajo21]

学到很多东西，感谢大家...

[minajo21]

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

[zjcxc]

--加多一层循环就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

[didoleo]

哦,懂了,如果传入一个子结点,那第一个循环@re是空的

[didoleo]

如果传入一个父结点,那第二个循环@re不再插入任何值