高手高手快进来帮帮忙.在线等
已知父子关系记录存在于一张表中：
__________________________________________
id       |       Parent_id       |       Name
------------------------------------------
1         |       -1                     |       张三
------------------------------------------
2         |       1                       |       里斯
------------------------------------------
3         |       1                       |       王武
------------------------------------------
4         |       2                       |       赵六
------------------------------------------
5         |       3                       |       牛七
------------------------------------------
.......

用一个存储过程，计算出某用户下级共有多少子记录  

P_GetUsersScore   @User_id   bigint,   @Score   int   output
AS
.....
.....
set   @Score   =   xxxx
GO

------解决方案--------------------
--建立測試環境
Create Table Tree
(id bigint,
Parent_id bigint,
Name Nvarchar(10))
Insert Tree Select 1, -1, N '张三 '
Union All Select 2, 1, N '里斯 '
Union All Select 3, 1, N '王武 '
Union All Select 4, 2, N '赵六 '
Union All Select 5, 3, N '牛七 '
GO
--建立存儲過程
Create Procedure P_GetUsersScore @User_id bigint, @Score int output
AS
Select * Into #T From Tree Where id = @User_id
While @@ROWCOUNT > 0
Insert #T Select A.* From Tree A Inner Join #T B On A.Parent_id = B.id Where A.id Not In (Select Distinct id From #T)
Select @Score = Count(*) - 1 From #T
Drop Table #T
GO
--測試
Declare @Score int
EXEC P_GetUsersScore 1, @Score output
Select @Score As Score
EXEC P_GetUsersScore 2, @Score output
Select @Score As Score
GO
--刪除測試環境
Drop Table Tree
Drop Procedure P_GetUsersScore
--結果
/*
Score
4

Score
1
*/