查找树型结构的根结点
查找树型结构的根结点.

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


------解决方案--------------------
--提供笨方法供參考,cursor+function

create function fn_pid(@id varchar(10))
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
End

GO

create 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
*/


------解决方案--------------------
还是用sql2005 的CTE公用表来做可能要简单些


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 '