--創建測試環境 
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 
*/

Create   Table   表1 
(单号   Int, 
  项目1   Varchar(10)) 
Insert   表1   Select     1,             'A ' 
Union   All   Select     1,             'B ' 
Union   All   Select     2,             'A ' 
Union   All   Select     2,             'B ' 
Union   All   Select     3,             'C ' 

Create   Table   表2 
(单号   Int, 
  项目2   Varchar(10)) 
Insert   表2   Select     1,             'A ' 
Union   All   Select     1,             'C ' 
Union   All   Select     2,             'C ' 
Union   All   Select     4,             'A ' 
GO 
Select 
IsNull(A.单号,   B.单号)   As   单号, 
A.项目1, 
B.项目2 
From 
表1   A 
Full   Join 
表2   B 
On   A.单号   =   B.单号   And   A.项