--這個有什麼問題?--建立測試環境
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
4Score
1
*/
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
4Score
1
*/
1 -1 张三
2 1 里斯
3 1 王武
4 2 赵六
5 3 牛七
。
用一个存储过程,计算出某用户下级共有多少子记录(下级的下级...所有子级) P_GetUsersScore @User_id bigint, @Score int output AS ..... ..... set @Score = xxxx GO 注:级别层数可能会超过100层,注意性能。
insert into #t(id) values(@User_id )while exists(select id from table where parent_id in(select id from #t) and id not in(select id from #t))
begin
insert into #t(id)
select id
from table where parent_id in(select id from #t) and id not in(select id from #t)
endselect count(*) from #t