有如下表Users(用户编号、父级编号、点值)
UserId ParentId Points
1 0 0
2 1 0
3 0 0
4 2 0
6 4 0
7 6 0现在想实现更新UserId=7的用户他的父级编号的隔一个上面2层有父子关系的有用户点值+1,比如最后结果如果
UserId ParentId Points
1 0 0
2 1 1
3 0 0
4 2 1
6 4 0
7 6 0就是id7的用户父级是id6,但是id6用户不加点,id6的的前2层父级,id为4和2的加点,同时还要考虑是否父级编号和是否达到两层等问题,很头大,谁能解答啊
UserId ParentId Points
1 0 0
2 1 0
3 0 0
4 2 0
6 4 0
7 6 0现在想实现更新UserId=7的用户他的父级编号的隔一个上面2层有父子关系的有用户点值+1,比如最后结果如果
UserId ParentId Points
1 0 0
2 1 1
3 0 0
4 2 1
6 4 0
7 6 0就是id7的用户父级是id6,但是id6用户不加点,id6的的前2层父级,id为4和2的加点,同时还要考虑是否父级编号和是否达到两层等问题,很头大,谁能解答啊
父零件代码 零件代码
A A1
A1 A11
A1 A12
A12 A121
A A2
B B1
B1 B11
B1 B12
B B2
需要得到的结果=>
顶层零件 父零件代码 零件代码 层次
A A A1 1
A A1 A11 2
A A1 A12 2
A A12 A121 3
A A A2 1
B B B1 1
B B1 B11 2
B B1 B12 2
B B B2 1 说明得到的结果:遍历所有顶层的零件,将其下面所有隶属的零件按照父子关系列出来
--------------------------------------------------------------create table tb(pid varchar(10), cid varchar(10))
insert into tb values('A' , 'A1')
insert into tb values('A1' , 'A11')
insert into tb values('A1' , 'A12')
insert into tb values('A12' , 'A121')
insert into tb values('A' , 'A2')
insert into tb values('B' , 'B1')
insert into tb values('B1' , 'B11')
insert into tb values('B1' , 'B12')
insert into tb values('B' , 'B2')
gocreate function f_pid(@cid varchar(10)) returns varchar(20)
as
begin
declare @re_str as varchar(10)
set @re_str = ''
declare @level as int
set @level = 0
select @re_str = pid from tb where cid = @cid
while exists (select 1 from tb where cid = @re_str)
begin
select @re_str = pid from tb where cid = @re_str
set @level = @level + 1
end
return @re_str + ',' + cast(@level + 1 as varchar)
end
goselect 顶层零件 = left(dbo.f_pid(cid),charindex(',',dbo.f_pid(cid))-1) ,
父零件代码 = pid ,
零件代码 = cid ,
substring(dbo.f_pid(cid),charindex(',',dbo.f_pid(cid))+1 , len(dbo.f_pid(cid))) 层次 from tbdrop table tbdrop function dbo.f_pid/*
顶层零件 父零件代码 零件代码 层次
-------------------- ---------- ---------- --------------------
A A A1 1
A A1 A11 2
A A1 A12 2
A A12 A121 3
A A A2 1
B B B1 1
B B1 B11 2
B B1 B12 2
B B B2 1(所影响的行数为 9 行)
*/
select 1,0,0 union all
select 2,1,0 union all
select 3,0,0 union all
select 4,2,0 union all
select 6,4,0 union all
select 7,6,0 --获取满足更新条件的两个节点中的子节点UserId
create function Get_pid(@sid int)
returns int
as
begin
declare @pid SMALLINT
select @pid=(select f.UserId
from tree f,tree s
where s.UserId=@sid AND s.ParentId=f.UserId And f.ParentId>0)
return @pid
end
declare @fid SMALLINT
declare @UserId SMALLINT
set @UserId=4select @UserId=(select ParentId from tree where UserId=@UserId)select @fid=dbo.Getpid(@UserId)
if(@fid is not [code=SQL]null)
begin
update tree
set points=points+1
where tree.UserId=@fid or tree.UserId=
(select ParentId
from tree
where UserId=@fid)
end
select * from tree[/code]
UserId tinyint,
ParentId tinyint,
Points smallint)insert into tree
select 1,0,0
union all
select 2,1,0
union all
select 3,0,0
union all
select 4,2,0
union all
select 6,4,0
union all
select 7,6,0
select * from tree
create function Get_pid(@sid int)
returns int
as
begin
declare @pid SMALLINT
select @pid=(select f.UserId
from tree f,tree s
where s.UserId=@sid AND s.ParentId=f.UserId And f.ParentId>0)
return @pid
end
declare @fid SMALLINT
declare @UserId SMALLINT
set @UserId=4select @UserId=(select ParentId from tree where UserId=@UserId)select @fid=dbo.Getpid(@UserId)
if(@fid is not null)
begin
update tree
set points=points+1
where tree.UserId=@fid or tree.UserId=
(select ParentId
from tree
where UserId=@fid)
end
select * from tree