接上次的那个循环递归的问题，另开了一帖

[minajo21]

还是上次的问题，还是没有达到我最终想要的效果，

那个帖子已经结了
http://community.csdn.net/Expert/topic/3775/3775616.xml?temp=.1938898


下面是实际的一段数据：
Lc1 Lc2 AssociationType
------------------------------------------------
20050131164410984641 20050131164432421370 05
20050131164410984641 20050131164543968868 02
20060131171500890142 20060131171220921358 08
20050124145844968972 20050125173637609394 04
20050124145844968972 20050125173626546785 04
20050124145844968972 20050125173615531281 04
20050124145844968972 20050125173602046303 04
20050124145844968972 20050125173545109045 01
20050124145844968972 20050125174025859974 04
20050125151927031220 20050126142401562322 05
20050125105700468193 20050126142854328194 04
20050126135747890253 20050125173156718720 05
20050203142012218583 20050203141830562810 05
20050125100042859651 20050125093343203803 04
20050125173451609257 20050125094929281422 04


我想用 '20050125173637609394' 查的时候，也能查到:
20050124145844968972 20050125173637609394 04
20050124145844968972 20050125173626546785 04
20050124145844968972 20050125173615531281 04
20050124145844968972 20050125173602046303 04
20050124145844968972 20050125173545109045 01
20050124145844968972 20050125174025859974 04

[minajo21]

根据 zjcxc(邹建) 的最后一次回复的方法

用'20050124145844968972'是正确的，如下的记录：
20050124145844968972 20050125173637609394 04
20050124145844968972 20050125173626546785 04
20050124145844968972 20050125173615531281 04
20050124145844968972 20050125173602046303 04
20050124145844968972 20050125173545109045 01
20050124145844968972 20050125174025859974 04

用'20050124145844968972' 只有一条：
20050124145844968972 20050125173637609394 04

[minajo21]

附 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

[minajo21]

顶！

这个帖子怎么还是看不到...

[子陌红尘]

--创建测试数据
create table tbl_LcAssociation (
Lc1 varchar(30) null,
Lc2 varchar(30) null,
AssociationType char(2) null
)

insert into tbl_lcassociation select '20050131164410984641','20050131164432421370','05'
insert into tbl_lcassociation select '20050131164410984641','20050131164543968868','02'
insert into tbl_lcassociation select '20060131171500890142','20060131171220921358','08'
insert into tbl_lcassociation select '20050124145844968972','20050125173637609394','04'
insert into tbl_lcassociation select '20050124145844968972','20050125173626546785','04'
insert into tbl_lcassociation select '20050124145844968972','20050125173615531281','04'
insert into tbl_lcassociation select '20050124145844968972','20050125173602046303','04'
insert into tbl_lcassociation select '20050124145844968972','20050125173545109045','01'
insert into tbl_lcassociation select '20050124145844968972','20050125174025859974','04'
insert into tbl_lcassociation select '20050125151927031220','20050126142401562322','05'
insert into tbl_lcassociation select '20050125105700468193','20050126142854328194','04'
insert into tbl_lcassociation select '20050126135747890253','20050125173156718720','05'
insert into tbl_lcassociation select '20050203142012218583','20050203141830562810','05'
insert into tbl_lcassociation select '20050125100042859651','20050125093343203803','04'
insert into tbl_lcassociation select '20050125173451609257','20050125094929281422','04'


--创建存储过程
create procedure sp_test(@LcID varchar(30))
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 distinct a.* from tbl_LcAssociation a,#t b where (a.Lc1 = b.LcID or a.Lc2 = b.LcID) and b.type > 0
end

--执行存储过程，执行结果自己看
exec sp_test '20050125173637609394'

[didoleo]

--这样你试一下看看
create function f_id(@LcID varchar(20))
returns @re table(LcID varchar(20),level int)
as
begin
declare @er table(LcIDp varchar(20),level int)
declare @l int
declare @lp int
set @l=0
set @lp=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 @er select Lc1,@l from tbl_LcAssociation
where Lc2=@LcID
while @@rowcount>0
begin
set @lp=@lp+1
insert @er select a.Lc1,@l
from tbl_LcAssociation a,@er b
where a.Lc2=b.LcIDp and b.level=@lp-1
end

insert into @re select a.Lc2,b.level from tbl_LcAssociation a,@er b
where a.Lc1=b.LcIDp

return
end
go

[zjcxc]

--测试数据
create table tb(Lc1 char(20),Lc2 char(20),AssociationType char(2))
insert tb select '20050131164410984641','20050131164432421370','05'
union all select '20050131164410984641','20050131164543968868','02'
union all select '20060131171500890142','20060131171220921358','08'
union all select '20050124145844968972','20050125173637609394','04'
union all select '20050124145844968972','20050125173626546785','04'
union all select '20050124145844968972','20050125173615531281','04'
union all select '20050124145844968972','20050125173602046303','04'
union all select '20050124145844968972','20050125173545109045','01'
union all select '20050124145844968972','20050125174025859974','04'
union all select '20050125151927031220','20050126142401562322','05'
union all select '20050125105700468193','20050126142854328194','04'
union all select '20050126135747890253','20050125173156718720','05'
union all select '20050203142012218583','20050203141830562810','05'
union all select '20050125100042859651','20050125093343203803','04'
union all select '20050125173451609257','20050125094929281422','04'
go

--处理函数
create function f_id(@LcID char(20))
returns @re table(Lc1 char(20),Lc2 char(20))
as
begin
insert @re select Lc1,Lc2 from tb
where @LcID in(Lc1,Lc2)
while @@rowcount>0
insert @re select a.Lc1,a.Lc2
from tb a,@re b
where (b.Lc1 in(a.Lc1,a.Lc2) or b.Lc2 in(a.Lc1,a.Lc2))
and not exists(
select * from @re
where Lc1=a.Lc1 and Lc2=a.Lc2)
return
end
go

--调用
select a.*
from tb a,f_id('20050125173637609394')b
where a.Lc1=b.Lc1 and a.Lc2=b.Lc2
go

--删除测试
drop table tb
drop function f_id

/*--测试结果

Lc1 Lc2 AssociationType
-------------------- -------------------- ---------------
20050124145844968972 20050125173637609394 04
20050124145844968972 20050125173626546785 04
20050124145844968972 20050125173615531281 04
20050124145844968972 20050125173602046303 04
20050124145844968972 20050125173545109045 01
20050124145844968972 20050125174025859974 04

（所影响的行数为 6 行）
--*/

[豌豆干]

打个我的记号，以后好找。经典之作怎可放过

[didoleo]

create function f_id(@LcID varchar(20))
returns @re table(Lc1 varchar(20),LcID varchar(20),level int)
as
begin
declare @er table(LcIDp varchar(20),level int)
declare @l int
declare @lp int
set @l=0
set @lp=0
insert @re select Lc1,Lc2,@l from tbl_LcAssociation
where Lc1=@LcID
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.Lc1,a.Lc2,@l
from tbl_LcAssociation a,@re b
where a.Lc1=b.LcID and b.level=@l-1
end

insert @er select Lc1,@l from tbl_LcAssociation
where Lc2=@LcID
while @@rowcount>0
begin
set @lp=@lp+1
insert @er select a.Lc1,@l
from tbl_LcAssociation a,@er b
where a.Lc2=b.LcIDp and b.level=@lp-1
end

insert into @re select a.Lc1,a.Lc2,b.level from tbl_LcAssociation a,@er b
where a.Lc1=b.LcIDp

return
end
go

---调用方式

select a.Lc1,a.LcID as Lc2,b.AssociationType
from dbo.f_id('20050124145844968972') a,tbl_LcAssociation b
where a.Lc1=b.Lc1 and a.LcID=b.Lc2
select a.Lc1,a.LcID as Lc2,b.AssociationType
from dbo.f_id('20050125173626546785') a,tbl_LcAssociation b
where a.Lc1=b.Lc1 and a.LcID=b.Lc2


Lc1 Lc2 AssociationType
-------------------- -------------------- ---------------
20050124145844968972 20050125173637609394 04
20050124145844968972 20050125173626546785 04
20050124145844968972 20050125173615531281 04
20050124145844968972 20050125173602046303 04
20050124145844968972 20050125173545109045 01
20050124145844968972 20050125174025859974 04

（所影响的行数为 6 行）

Lc1 Lc2 AssociationType
-------------------- -------------------- ---------------
20050124145844968972 20050125173637609394 04
20050124145844968972 20050125173626546785 04
20050124145844968972 20050125173615531281 04
20050124145844968972 20050125173602046303 04
20050124145844968972 20050125173545109045 01
20050124145844968972 20050125174025859974 04

（所影响的行数为 6 行）

[minajo21]

万分感谢！！

[godhand2]

学习
呵呵