假设我有2个表T1和T2如下
T1：
bag_id bag_type capacity ------|--------|-------- 1 A 500 2 A 300 3 A 100 4 B 200 5 B 100
T2：
item_type item_amount ---------|----------- A 850 B 300
表T1中的每个记录表示一个行李及其容量,这里我有5个行李.我想写一个将表T2中的项目分配到相同类型的每个包中的sql,即结果应该是这样的
bag_id bag_type capacity allocated_amount ------|--------|--------|---------------- 1 A 500 500 2 A 300 300 3 A 100 50 4 B 200 200 5 B 100 100
因此,我找到某种聚合函数,我们称之为allocate(),它可以产生如上所述的列分配符.我有一个猜测,如果存在,它可能会这样使用
select
t1.bag_id,t1.bag_type,t1.capacity,allocate(t2.item_amount,t1.capacity)
over (partition by t1.bag_type order by t1.capacity desc) as allocatd_amount
from t1,t2
where t2.item_type = t1.bag_type
解决方法
你正在寻找一个累积的和.这样的东西
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;
