试试这个

Update @tb Set Outcome = 60 + Score Where sesion = 1

Declare @Session decimal(18,0)
Declare F Cursor For Select sesion From @tb Where sesion <> 1 Order By sesion
Open F
Fetch Next From F Into @MaID
While @@FETCH_STATUS = 0
Begin   
    Update @tb 
        Set Outcome = Case When Score + (Select Outcome From @tb Where sesion = @Session - 1) > 100 Then 100
                            When Score + (Select Outcome From @tb Where sesion = @Session - 1) < 0 Then 0
                            Else Score + (Select Outcome From @tb Where sesion = @Session - 1) End
        Where sesion = @Session
    Fetch Next From F Into @Session
End 
Close F
DeAllocate F

下面是一个使用递归CTE的解决方案：

with recursive cte (id, session_no, score, outcome) as (
select id, session_no, score, 60+score as outcome 
  from session_score 
 where session_no = 1
 union all
select ss.id, ss.session_no, ss.score,
       case
          when c.outcome + ss.score > 100 then 100
          when c.outcome + ss.score < 0 then 0
          else c.outcome + ss.score
       end as outcome       
  from session_score ss
  join cte c
    on ss.id = c.id
   and ss.session_no = c.session_no+1)
 select id, session_no, score, outcome
   from cte
  order by 1,2;

使用2个ID进行测试：

id    |session_no|score|outcome|
------+----------+-----+-------+
ST0127|         1|    2|     62|
ST0127|         2|    2|     64|
ST0127|         3|    2|     66|
ST0127|         4|    2|     68|
ST0127|         5|    2|     70|
ST0127|         6|    2|     72|
ST0127|         7|    2|     74|
ST0127|         8|    2|     76|
ST0127|         9|    2|     78|
ST0127|        10|    2|     80|
ST0127|        11|    2|     82|
ST0127|        12|    2|     84|
ST0127|        13|    2|     86|
ST0127|        14|    2|     88|
ST0127|        15|    2|     90|
ST0127|        16|    2|     92|
ST0127|        17|    2|     94|
ST0127|        18|    2|     96|
ST0127|        19|    2|     98|
ST0127|        20|    2|    100|
ST0127|        21|    2|    100|
ST0127|        22|    2|    100|
ST0127|        23|   -5|     95|
ST0127|        24|    2|     97|
ST0127|        25|    0|     97|
ST0127|        26|   -5|     92|
ST0127|        27|    2|     94|
ST0127|        28|    2|     96|
ST0127|        29|    2|     98|
ST0128|         1|    2|     62|
ST0128|         2|    2|     64|
ST0128|         3|    2|     66|
ST0128|         4|    2|     68|
ST0128|         5|    2|     70|
ST0128|         6|    2|     72|

对于相同的id，这将正常工作：

SELECT 
y.id,
y.nosession,
y.score,
@running_total := 
CASE WHEN @running_total + y.score > 100 THEN 100
WHEN @running_total + y.score < 0 THEN 0
ELSE @running_total + y.score END
AS cumulative_sum
FROM yourtable y
JOIN (SELECT @running_total := 60) r
ORDER BY y.id, y.nosession

这将适用于不同的ID：

SELECT sub.id, sub.nosession, sub.score, sub.cumulative_sum
FROM
(SELECT 
y.id,
y.nosession,
y.score,
@running_total := 
CASE WHEN id <> @id THEN 60 + y.score
WHEN @running_total + y.score > 100 THEN 100
WHEN @running_total + y.score < 0 THEN 0
ELSE @running_total + y.score END
AS cumulative_sum,
@id := y.id
FROM yourtable y
JOIN (SELECT @running_total := 60, @id:='0') r
ORDER BY y.id, y.nosession) sub;

请在此处试用：db<>fiddle