问如何选择一个固定值减去后的余值

EN

with T1 as 
(
select t.*,
SUM(Amount) OVER (PARTITION BY [Type] ORDER BY [SNo])
-
CASE WHEN Type='Type1' then 800
     WHEN Type='Type2' then 200
     WHEN Type='Type3' then 100
END as Total

from t
)select Sno,Type,
        CASE WHEN Amount>Total then Total 
                                    Else Amount
        end as Amount
from T1 where Total>0
order by Sno

CREATE TABLE types
    ([Type] varchar(5), [Value] int);

insert into types 
values
    ('type1',800),
    ('type2',200),
    ('type3',100);

with T1 as 
(
select t.*,
SUM(Amount) OVER (PARTITION BY t.[Type] ORDER BY [SNo])
-
ISNULL(types.Value,0) as Total

from t
left join types on (t.type=types.type) 
)select Sno,Type,
        CASE WHEN Amount>Total then Total 
                                    Else Amount
        end as Amount
from T1 where Total>0
order by Sno

with T1 as 
(
select t.*,
(select SUM(Amount) from t as t1 
    where t1.Type=t.Type 
     and t1.SNo<=t.SNo)  
-
ISNULL(types.Value,0) as Total

from t
left join types on (t.type=types.type) 
)select Sno,Type,
        CASE WHEN Amount>Total then Total 
                                    Else Amount
        end as Amount
from T1 where Total>0
order by Sno