可以实现,确实不想写了,给你我的一个例子 我查询部门ID ,张三对应的部门ID为8 ,如何查出张三的所有部门ID(包括父部门ID),如8,7,3,1--建立測試環境 Create Table department (departmenid Int, parentid Int) Insert department Select 60, null Union All Select 1, 0 Union All Select 2, 1 Union All Select 3, 1 Union All Select 4, 2 Union All Select 5, 2 Union All Select 6, 3 Union All Select 7, 3 Union All Select 8, 7 GO --建立函數 Create Function F_GetParent(@departmenid Int) Returns @Tree Table (departmenid Int, parentid Int) As Begin Insert @Tree Select * From department Where departmenid = @departmenid While @@Rowcount > 0 Insert @Tree Select A.* From department A Inner Join @Tree B On A.departmenid = B.parentid And A.departmenid Not In (Select departmenid From @Tree) Where A.parentid Is Not Null Return End GO --測試 Select departmenid From dbo.F_GetParent(8) Order By parentid GO --刪除測試環境 Drop Table department Drop Function F_GetParent --結果 /* departmenid 1 3 7 8 */
我查询部门ID ,张三对应的部门ID为8 ,如何查出张三的所有部门ID(包括父部门ID),如8,7,3,1--建立測試環境
Create Table department
(departmenid Int,
parentid Int)
Insert department Select 60, null
Union All Select 1, 0
Union All Select 2, 1
Union All Select 3, 1
Union All Select 4, 2
Union All Select 5, 2
Union All Select 6, 3
Union All Select 7, 3
Union All Select 8, 7
GO
--建立函數
Create Function F_GetParent(@departmenid Int)
Returns @Tree Table (departmenid Int, parentid Int)
As
Begin
Insert @Tree Select * From department Where departmenid = @departmenid
While @@Rowcount > 0
Insert @Tree Select A.* From department A Inner Join @Tree B On A.departmenid = B.parentid And A.departmenid Not In (Select departmenid From @Tree) Where A.parentid Is Not Null
Return
End
GO
--測試
Select departmenid From dbo.F_GetParent(8) Order By parentid
GO
--刪除測試環境
Drop Table department
Drop Function F_GetParent
--結果
/*
departmenid
1
3
7
8
*/