--示例数据：
create table tb(
Lc1 char(20), --Lc1与Lc2形成上下级关系
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
/*--问题描述
表结构说明:
Lc1与Lc2形成上下级关系,例如,对于 20050125173637609394
记录
20050124145844968972 20050125173637609394 04
是直接关系的记录
而记录
20050124145844968972 20050125173626546785 04
20050124145844968972 20050125173615531281 04
20050124145844968972 20050125173602046303 04
20050124145844968972 20050125173545109045 01
20050124145844968972 20050125174025859974 04
是与其有间接关系的记录
查询要求:
输入指定的Lc(可以是Lc1,也可以是Lc2)值,可以查询与与该值相关的所有Lc1,Lc2的记录
例如,查询 '20050125173637609394' 的时候，能查到:
20050124145844968972 20050125173637609394 04
20050124145844968972 20050125173626546785 04
20050124145844968972 20050125173615531281 04
20050124145844968972 20050125173602046303 04
20050124145844968972 20050125173545109045 01
20050124145844968972 20050125174025859974 04
--*/
---------------------------------------------------------------
--方法1.
--创建存储过程
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
go
--执行存储过程，执行结果自己看
exec sp_test '20050125173637609394'
go
---------------------------------------------------------------
--方法2.
--处理函数
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