关系表结构A
-------------------------
ID fatherID Name
1 NULL tt
2 1 aa
3 1 bb
4 2 cc
5 2 gg
6 4 yy
7 4 jj
8 7 ll
9 NULL uu
10 9 oo
-------------------------------
现在我需要遍历这个表,把所有faterhID 1下的所有子节点的数据导到另一个表B,请问如何写遍历函数。
-------------------------
ID fatherID Name
1 NULL tt
2 1 aa
3 1 bb
4 2 cc
5 2 gg
6 4 yy
7 4 jj
8 7 ll
9 NULL uu
10 9 oo
-------------------------------
现在我需要遍历这个表,把所有faterhID 1下的所有子节点的数据导到另一个表B,请问如何写遍历函数。
Create Function GetChildren(@ID Int)
Returns @Tree Table (ID Int, fatherID Int, Name Varchar(10))
As
Begin
Insert @Tree Select ID, fatherID, Name From A Where fatherID = @ID
While @@Rowcount > 0
Insert @Tree Select A.ID, A.fatherID, A.Name From A A Inner Join @Tree B On A.fatherID = B.ID And A.ID Not In (Select ID From @Tree)
Return
End
GO
--測試
Select * From dbo.GetChildren(1)
Create Table A
(ID Int,
fatherID Int,
Name Varchar(10)
)
Insert A Select 1, NULL, 'tt'
Union All Select 2, 1, 'aa'
Union All Select 3, 1, 'bb'
Union All Select 4, 2, 'cc'
Union All Select 5, 2, 'gg'
Union All Select 6, 4, 'yy'
Union All Select 7, 4, 'jj'
Union All Select 8, 7, 'll'
Union All Select 9, NULL, 'uu'
Union All Select 10, 9, 'oo'
GO
--建立函數
Create Function GetChildren(@ID Int)
Returns @Tree Table (ID Int, fatherID Int, Name Varchar(10))
As
Begin
Insert @Tree Select ID, fatherID, Name From A Where fatherID = @ID
While @@Rowcount > 0
Insert @Tree Select A.ID, A.fatherID, A.Name From A A Inner Join @Tree B On A.fatherID = B.ID And A.ID Not In (Select ID From @Tree)
Return
End
GO
--測試
Select * From dbo.GetChildren(1)
GO
--刪除測試環境
Drop Table A
Drop Function GetChildren
--結果
/*
ID fatherID Name
2 1 aa
3 1 bb
4 2 cc
5 2 gg
6 4 yy
7 4 jj
8 7 ll
*/
Private Sub Command1_Click()
SQL = "select ID from A WHERE fatherid ='1'"
adoRs.Open SQL, DB, adOpenDynamic, adLockReadOnly
While Not adoRs.EOF
Junior (adoRs!ID)
adoRs.MoveNext
Wend
adoRs.Close
End Sub'遍历
Private Function Junior(ByVal strID As String) SQL = "select ID from A where father='"& strID &"'"
adoRs1.Open SQL, DB, 3, 1, 1
While Not adoRs1.EOF SQL = "insert into B(ID,fartherID,Name)"
SQL = SQL & "SELECT ID,fartherID,Name from A where ID ='" & strTH & "'" Junior (adoRs1!DH)
adoRs1.MoveNext
Wend
adoRs1.CloseEnd Function这样写递归遍历是有问题的 adors1会被多次打开!