--查詢結果中包含當前節點--建立測試環境 Create Table TEST (ID Int, ParentID Int, [Text] Varchar(10)) Insert TEST Select 1, 0, 'A1' Union All Select 2, 1, 'A2' Union All Select 3, 1, 'A3' Union All Select 4, 2, 'A4' Union All Select 5, 3, 'A5' Union All Select 6, 5, 'A6' GO --建立存儲過程 Create ProceDure SP_GetParent(@ID Int) As Begin Select * Into #T From TEST Where ID = @ID While @@ROWCOUNT > 0 Insert #T Select A.* From TEST A Inner Join #T B On A.ID = B.ParentID And A.ID Not In (Select ID From #T) Select * From #T Order By ID Drop Table #T End GO --測試 EXEC SP_GetParent 5 EXEC SP_GetParent 6 GO --刪除測試環境 Drop Table TEST Drop ProceDure SP_GetParent --結果 /* ID ParentID Text 1 0 A1 3 1 A3 5 3 A5ID ParentID Text 1 0 A1 3 1 A3 5 3 A5 6 5 A6 */
Create Table TEST
(ID Int,
ParentID Int,
[Text] Varchar(10))
Insert TEST Select 1, 0, 'A1'
Union All Select 2, 1, 'A2'
Union All Select 3, 1, 'A3'
Union All Select 4, 2, 'A4'
Union All Select 5, 3, 'A5'
Union All Select 6, 5, 'A6'
GO
--建立存儲過程
Create ProceDure SP_GetParent(@ID Int)
As
Begin
Select * Into #T From TEST Where ID = @ID
While @@ROWCOUNT > 0
Insert #T Select A.* From TEST A Inner Join #T B On A.ID = B.ParentID And A.ID Not In (Select ID From #T)
Select * From #T Order By ID
Drop Table #T
End
GO
--測試
EXEC SP_GetParent 5
EXEC SP_GetParent 6
GO
--刪除測試環境
Drop Table TEST
Drop ProceDure SP_GetParent
--結果
/*
ID ParentID Text
1 0 A1
3 1 A3
5 3 A5ID ParentID Text
1 0 A1
3 1 A3
5 3 A5
6 5 A6
*/