create table message
(
MemberID varchar(10),
ParentID varchar(10),
Points int
)
create table info
(
MemberID varchar(10),
Name varchar(10),
Address varchar(100)
)
insert message
select 'A',null,10 union
select 'B','A',20 union
select 'C','B',0 union
select 'D','C',30
insert info
select 'A','rose','xxxxx' union
select 'B','marry','sdsdsd' union
select 'C','mike','dsad' union
select 'D','john','lksd'
go--创建函数
create function f_points
(
@MemberID varchar(10), --MemberID
@Points int=0 --点击数
)
returns int
as
begin
select @Points=isnull(@Points,0)+Points
from message where MemberID=@MemberID if (select ParentID from message where MemberID=@MemberID) is null
return @Points return dbo.f_points((select ParentID from message where MemberID=@MemberID),@Points)
end
go--调用
select A.MemberID
,B.Name
,B.Address
,A.ParentID
,dbo.f_points(A.MemberID,0) as 'MemberPoints'
from message A
join info B on A.MemberID=B.MemberID
where A.MemberID='D'--删除测试环境
drop function f_points
drop table message,info--结果
/*
MemberID Name Address ParentID MemberPoints
---------- ---------- ----------------------------------------
D john lksd C 60(1 row(s) affected)
*/
(
MemberID varchar(10),
ParentID varchar(10),
Points int
)
create table info
(
MemberID varchar(10),
Name varchar(10),
Address varchar(100)
)
insert message
select 'A',null,10 union
select 'B','A',20 union
select 'C','B',0 union
select 'D','C',30
insert info
select 'A','rose','xxxxx' union
select 'B','marry','sdsdsd' union
select 'C','mike','dsad' union
select 'D','john','lksd'
go--创建函数
create function f_points
(
@MemberID varchar(10), --MemberID
@Points int=0 --点击数
)
returns int
as
begin
select @Points=isnull(@Points,0)+Points
from message where MemberID=@MemberID if (select ParentID from message where MemberID=@MemberID) is null
return @Points return dbo.f_points((select ParentID from message where MemberID=@MemberID),@Points)
end
go--调用
select A.MemberID
,B.Name
,B.Address
,A.ParentID
,dbo.f_points(A.MemberID,0) as 'MemberPoints'
from message A
join info B on A.MemberID=B.MemberID
where A.MemberID='D'--删除测试环境
drop function f_points
drop table message,info--结果
/*
MemberID Name Address ParentID MemberPoints
---------- ---------- ----------------------------------------
D john lksd C 60(1 row(s) affected)
*/
回去我细细看,,,好好学习,天天向上,,呵呵谢了。。