查找树型结构的根结点.一个table t1 保存着多叉树型结构,比如
P_cid c_cid
--------------
A C
B C
C D
C E
F E另一个table t2 保存需要查找的节点,比如
cid
----
D
E请问如何查出这个结果?谢啦
cid top_cid
---------------
D A
D B
E A
E B
E F
P_cid c_cid
--------------
A C
B C
C D
C E
F E另一个table t2 保存需要查找的节点,比如
cid
----
D
E请问如何查出这个结果?谢啦
cid top_cid
---------------
D A
D B
E A
E B
E F
returns @t table(id varchar(10),level int)
As
Begin
declare @level int
set @level=1
insert into @t select @id,@level
while @@rowcount>0
begin
set @level=@level+1
insert @t select a.p_cid,@level
from t1 a,@t b
where a.c_cid=b.id
and b.level=@level-1
end
return
EndGOcreate table # (cid varchar(10),top_cid varchar(10))select p_cid
into #top
from t1 a
where not exists(select 1 from t1 where c_cid=a.p_cid)declare @cid varchar(10)
declare c1 cursor for
select cid from t2
open c1
fetch next from c1 into @cid
while @@fetch_status=0
begin
insert into #
select @cid,id from dbo.fn_cid(@cid)
where exists (select 1 from #top where p_cid =id) fetch next from c1 into @cid
end
close c1
deallocate c1
Create Table T1
(P_cid Varchar(10),
c_cid Varchar(10))
Insert T1 Select 'A', 'C'
Union All Select 'B', 'C'
Union All Select 'C', 'D'
Union All Select 'C', 'E'
Union All Select 'F', 'E'Create Table T2
(cid Varchar(10))
Insert T2 Select 'D'
Union All Select 'E'
GO
--創建函數
Create Function F_GetChildren()
Returns @Tree Table(cid Varchar(10), top_cid Varchar(10))
As
Begin
Insert @Tree Select Distinct A.c_cid, A.P_cid From T1 A Inner Join T2 B On A.c_cid = B.cid
While @@ROWCOUNT > 0
Insert @Tree Select Distinct B.cid, A.P_cid From T1 A Inner Join @Tree B On A.c_cid = B.top_cid Where A.P_cid Not In (Select top_cid From @Tree)
Delete A From @Tree A Inner Join T1 B On A.top_cid = B.P_cid Left Join T1 C On B.P_cid = C.c_cid Where C.P_cid Is Not Null
Return
End
GO
--測試
Select * From dbo.F_GetChildren() Order By cid, top_cid
GO
--刪除測試環境
Drop Table T1, T2
Drop Function F_GetChildren
--結果
/*
cid top_cid
D A
D B
E A
E B
E F
*/
(P_cid Varchar(10),
c_cid Varchar(10))
Insert T1 Select 'A', 'C'
Union All Select 'B', 'C'
Union All Select 'C', 'D'
Union All Select 'C', 'E'
Union All Select 'F', 'E'Create Table T2
(cid Varchar(10))
Insert T2 Select 'D'
Union All Select 'E'
GO
;with cte as
(select cid,p_cid,c_cid, 0 level
from t1
join t2 on t1.c_cid=t2.cid union all
select cte.cid,t1.p_cid,t1.c_cid,level+1 level
from t1
join cte on t1.c_cid=cte.p_cid
)
select cid,p_cid top_cid
from cte
where not exists (select * from t1 where c_cid=cte.p_cid)
order by 1,2
Drop Table T1, T2/*(5 行受影响)(2 行受影响)
cid top_cid
---------- ----------
D A
D B
E A
E B
E F(5 行受影响)*/