如图:
Id Name ParentId
1 N_1 0
2 N_2 1
3 N_3 1
4 N_4 3
5 N_5 2
6 N_6 2
7 N_7 4
8 N_8 7
9 N_9 8
10 N_10 8
.
.
.此树结构为:
N_1
----N_2
----N_5
----N_6
----N_3
----N_4
----N_7
----N_8
----N_9
----N_10
----N_....如何只读取N_3的前3级,急、急、急!
Id Name ParentId
1 N_1 0
2 N_2 1
3 N_3 1
4 N_4 3
5 N_5 2
6 N_6 2
7 N_7 4
8 N_8 7
9 N_9 8
10 N_10 8
.
.
.此树结构为:
N_1
----N_2
----N_5
----N_6
----N_3
----N_4
----N_7
----N_8
----N_9
----N_10
----N_....如何只读取N_3的前3级,急、急、急!
--建立測試環境
Create Table Tree(
Id Int Identity(1,1),
Name Varchar(10),
ParentId Int)
--插入數據
Insert Tree Select 'N_1', 0
Union All Select 'N_2', 1
Union All Select 'N_3', 1
Union All Select 'N_4', 3
Union All Select 'N_5', 2
Union All Select 'N_6', 2
Union All Select 'N_7', 4
Union All Select 'N_8', 7
Union All Select 'N_9', 8
Union All Select 'N_10', 8
Go
--建立函數
Create Function GetChild(@ParentId Int,@Rank Int)
Returns @Rout Table (Id Int,Name Varchar(10),ParentId Int,Rank Int)
As
Begin
Declare @I Int
Set @I=0
Insert @Rout Select *,0 From Tree Where Id=@ParentId
While @@RowCount>0 And @I+1<=@Rank
Begin
Select @I=@I+1
Insert @Rout Select A.*, @I From Tree A Inner Join @Rout B On A.ParentId=B.Id Where A.ParentId Not In (Select Distinct ParentId From @Rout)
End
Return
End
GO
--測試
Select *From dbo.GetChild(1,3)
Select *From dbo.GetChild(3,3)
GO
--刪除測試環境
Drop Table Tree
Drop Function GetChild
GO
--結果
/*
Id Name ParentId Rank
1 N_1 0 0
2 N_2 1 1
3 N_3 1 1
5 N_5 2 2
6 N_6 2 2
4 N_4 3 2
7 N_7 4 3Id Name ParentId Rank
3 N_3 1 0
4 N_4 3 1
7 N_7 4 2
8 N_8 7 3
*/