27,580
社区成员
发帖
与我相关
我的任务
分享
SELECT *
INTO #tmp
FROM
(
SELECT * ,ISNULL((
SELECT SUM(Amount) FROM @Result WHERE grp LIKE a.grp+'%' AND Row>a.Row
),0) as totalamount
FROM @Result a
)t
WHERE totalamount is not null
order by grp,usertype
SELECT Row,userid,name,userbagid,usertype,
totalamount,
leftamout=(select top 1 amount+totalamount from #tmp where grp LIKE a.grp+'%' AND lvl=a.lvl+1 and usertype='left'),
rightamout=(select top 1 amount+totalamount from #tmp where grp LIKE a.grp+'%' AND lvl=a.lvl+1 and usertype='right')
FROM #tmp a
WHERE Totalamount>0
drop table #tmp
/*
Row userid name userbagid usertype totalamount leftamout rightamout
----------- ---------- ---------- ---------- ---------- ----------- ----------- -----------
1 1 x 001 0 9900 5000 4900
2 2 t1 1 left 4000 2200 1800
4 4 a1 2 left 1400 700 700
5 5 a2 2 right 1100 600 500
3 3 t2 1 right 4100 2200 1900
6 6 a3 3 left 1400 700 700
7 7 a4 3 right 1100 600 500
*/