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',null
create 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
