您正在寻找一个累加的总和。像这样的东西：
select t1.*,
(case when cumecap <= t2.item_amount
then t1.capacity
when cumecap - t1.capacity <= t2.item_amount
then t2.item_amount - (cumecap - t1.capacity)
else 0
end) as allocated_capacity
from (select t1.*,
sum(t1.capacity) over (partition by bag_type order by bag_id) as cumecap
from t1
) t1 join
t2
on t1.bag_type = t2.item_type;
602392714
清零编程群