SQL遍历父子关系表的方法未必人人都知道，下面就为您介绍一个SQL遍历父子关系表的测试，希望可以让您对SQL遍历父子关系表有更深的认识 。
--建立测试环境
- 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
--结果
- /*
- IDfatherIDName
- 21aa
- 31bb
- 42cc
- 52gg
- 64yy
- 74jj
- 87ll
- */
