选择 sum 直到设定数量，然后更新 mysql 数据库中的字段 [英] Select sum until a set amount and then update fields in mysql database

问题描述

 item_id      rate            status
 ---------    -----------     ------
   1           12              credit
   2           10              credit
   3           10              credit
   4           20              cash
   5           55              credit

我有上面的表格，一个用户输入和 25 的金额.现在我想更新具有信用作为状态的行的状态，从信用到现金，直到利率总和为 25，所以在上表中总和为 22 的 1 行应获得现金状态.由于用户输入是 25，我仍然有 3 的余额(25-22)，这个余额应该从第三行中扣除，使第三行的比率为 7.我想要的结果是表格形式，其中突出显示了更改:

I have the above table, A user inputs and amount of 25. Now I want to update the status of the rows having credit as status from credit to cash until the sum of rate is 25, so in the above table the top 1 rows having a sum of 22 should get a status of cash. Since the user input is 25, I still have a balance of 3 (25-22), this balance should be deducted from the third row making the third row rate 7. The result I want is tabular form with the changes highlighted:

 item_id      rate            status
 ---------    -----------     ------
   1           12              **cash**
   2           10              **cash**
   3           **7**           credit
   4           20              cash
   5           55              credit

推荐答案

您可以使用窗口函数来识别需要更改的行:

You can use window functions to identify the rows that need to be changed:

select item_id, 
    case when sum_rate >= 25 then 'credit' else 'cash' end as status, 
    case when sum_rate >= 25 then sum_rate - 25 else rate end as rate
from (
    select t.*, sum(rate) over(order by item_id) sum_rate
    from mytable t
    where status = 'credit'
) t
where sum_rate - rate < 25

如果您愿意，可以将该逻辑放在 update 语句中:

You can put that logic in an update statement if you prefer:

update mytable t
inner join (
    select item_id, sum(rate) over(order by item_id) sum_rate
    from mytable t
    where status = 'credit'
) t1 on t1.item_id = t.item_id
set 
    t.status = case when sum_rate >=  25 then 'credit' else 'cash' end,
    t.rate =   case when t1.sum_rate >= 25 then t1.sum_rate - 25 else t.rate end
where t1.sum_rate - t.rate < 25

DB Fiddle 演示

这篇关于选择 sum 直到设定数量，然后更新 mysql 数据库中的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！