如何在sql thru过程中获取记录并更新其上层 [英] how to get record and update its upper level in sql thru procedure
本文介绍了如何在sql thru过程中获取记录并更新其上层的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
你好。每个人,
i我的存储过程有问题
insert into tbl_level(Parentid,childid,rewardpoint,earnmoney,noofchild) values ( @ parentkey , @ CusId , 1 , convert ( decimal ,(@ BookingAmt / 10)), 1 )
如果 存在(选择 1 来自 tbl_level 其中 childid = @ parentkey)
开始
update tbl_level < span class =code-keyword> set earnmoney = earnmoney + @ earnmoney 其中 parentid =(选择 Parentid 来自 tbl_level 其中 Childid = @ parentkey)
end
表有parentid和childid和earnmoney列现在有功能,当任何记录插入它的父母得到佣金,我希望其父母的父母获得佣金高达4级。
例如A有一个孩子B和B有孩子C,C有孩子D所以我想当D成为Child E佣金的父母时,应该分别去D,C,B,A。
解决方案
Tadit Dash解决方案很好,但是当您拥有过多的父母/孩子时会发生什么。
一个解决方案是使用while循环使用exists关键字而不是使用如果
喜欢
声明 @nextParent int ;
插入 进入 tbl_level(Parentid,childid,rewardpoint,earnmoney,noofchild) 值( @ parentkey , @ CusId , 1 , convert ( decimal ,(@ BookingAmt / 10)), 1 )
而 存在(选择 @ nextParent = childid 来自 tbl_level where childid = @ parentkey)
开始
update tbl_level set earnmoney = earnmoney + @ earnmoney 其中 parentid =(选择 Parentid 来自 tbl_level 其中 Childid = @ parentkey)
结束
试试这个
< span class =code-keyword>如果存在(选择 1 来自 tbl_level 其中 childid = @ parentkey)
begin
insert 进入 tbl_level(Parentid,childid,rewardpoint,earnmoney,noofchild)值(@ parentkey,@ CusId, 1 ,convert( decimal ,(@ BookingAmt / 10)), 1 )
update tbl_level set earnmoney = earnmoney + @ earnmoney 其中 parentid =(选择 Parentid 来回m tbl_level 其中 Childid = @ parentkey)
结束
尝试如下所示。
声明 @ nextParent int ;
插入 进入 tbl_level(Parentid,childid,rewardpoint,earnmoney,noofchild) 值( @ parentkey , @ CusId , 1 , convert ( decimal ,(@ BookingAmt / 10)), 1 )
if 存在(选择 1 来自 tbl_level 其中 childid = @ parentkey)
开始
选择 @ nextParent = childid 来自 tbl_level 其中 childid = @ parentkey
update tbl_level set earnmoney = earnmoney + @ earnmoney where parentid =(选择 Parentid 来自 tbl_level 其中 Childid = @ parentkey)
如果 存在( select 1 来自 tbl_level 其中 childid = @ nextParent)
开始
选择 @ nextParent = childid 来自 tbl_level 其中 childid = @ nextParent
update tbl_level set earnmoney = earnmoney + @ earnmoney其中 parentid =(选择 Parentid 来自 tbl_level < span class =code-keyword> where Childid = @ nextParent)
end
- 继续此流程...
end
hello Everyone,
i have a problem in my stored procedure
insert into tbl_level(Parentid,childid,rewardpoint,earnedmoney,noofchild) values(@parentkey,@CusId,1,convert(decimal,(@BookingAmt/10)),1)
if exists (select 1 from tbl_level where childid=@parentkey)
begin
update tbl_level set earnedmoney= earnedmoney+@earnedmoney where parentid=(select Parentid from tbl_level where Childid=@parentkey)
end
table has parentid and childid and earnedmoney columns now there is functionality when any record get inserted it's parent get commission , i want its parent's parents get commission upto 4 level.
e.g A has a child B and B has child C and C has child D so i want when D become parent of Child E commission amount should be go to D,C,B,A respectively.
解决方案
Tadit Dash Solution Is Good But What Happened When You Have Excessive Number Of Parents/Childs.
One Solution Is To Use While Loop With exists keyword instead of using if
like
Declare @nextParent int; insert into tbl_level(Parentid,childid,rewardpoint,earnedmoney,noofchild) values(@parentkey,@CusId,1,convert(decimal,(@BookingAmt/10)),1) while exists (select @nextParent=childid from tbl_level where childid=@parentkey) begin update tbl_level set earnedmoney= earnedmoney+@earnedmoney where parentid=(select Parentid from tbl_level where Childid=@parentkey) end
try this
if exists (select 1 from tbl_level where childid=@parentkey) begin insert into tbl_level(Parentid,childid,rewardpoint,earnedmoney,noofchild) values(@parentkey,@CusId,1,convert(decimal,(@BookingAmt/10)),1) update tbl_level set earnedmoney= earnedmoney+@earnedmoney where parentid=(select Parentid from tbl_level where Childid=@parentkey) end
Try something like below.
Declare @nextParent int; insert into tbl_level(Parentid,childid,rewardpoint,earnedmoney,noofchild) values(@parentkey,@CusId,1,convert(decimal,(@BookingAmt/10)),1) if exists (select 1 from tbl_level where childid=@parentkey) begin select @nextParent=childid from tbl_level where childid=@parentkey update tbl_level set earnedmoney= earnedmoney+@earnedmoney where parentid=(select Parentid from tbl_level where Childid=@parentkey) if exists (select 1 from tbl_level where childid=@nextParent) begin select @nextParent=childid from tbl_level where childid=@nextParent update tbl_level set earnedmoney= earnedmoney+@earnedmoney where parentid=(select Parentid from tbl_level where Childid=@nextParent) end -- Continue this flow... end
这篇关于如何在sql thru过程中获取记录并更新其上层的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文