选择总和直到一定数量，然后根据条件更新某些字段 [英] select sum until certain amount and then update certain fields based on condition

问题描述

我有一个查询会更改某些字段的状态和另一个字段的比率.

I have a query that changes the status of some fields and rate of another field.

小提琴示例

CREATE TABLE mytable(
   item_id INTEGER  NOT NULL PRIMARY KEY 
  ,rate    INTEGER  NOT NULL
  ,status  VARCHAR(6) NOT NULL
);
INSERT INTO mytable(item_id,rate,status) VALUES (1,12,'credit');
INSERT INTO mytable(item_id,rate,status) VALUES (2,10,'credit');
INSERT INTO mytable(item_id,rate,status) VALUES (3,10,'credit');
INSERT INTO mytable(item_id,rate,status) VALUES (4,20,'cash');
INSERT INTO mytable(item_id,rate,status) VALUES (5,55,'credit');

select item_id, 'cash' as status, 
    case when sum_rate >= 23 then sum_rate - 23 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 < 23;


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 = 'cash',
    t.rate = case when t1.sum_rate >= 23 then t1.sum_rate - 23 else t.rate end
where t1.sum_rate - t.rate < 23

逻辑检查所有行的总和，直到达到值 23 并将这些行的状态更改为 cash，在示例中，因为 rate 的总和在前 3 行中大于 23，第三行在添加前两行后用余额更新.我希望第三行 status 保持不变，只更新 rate.

The logic checks the sum of all rows until a value of 23 is reached and changes the status of those rows to cash, in the example since the total of rate in top 3 rows is greater than 23, the third row is updated with the balance after adding the first two rows. I want the third row status to remain the same and only the rate to be updated.

上述代码的问题在于，它更新了 rate 总和为 23 的所有行的所有 status.

The problem the above code is that it updates all the status of all rows having sum of rate 23.

原题供参考

推荐答案

好的，我能够通过对查询添加以下内容来完成它，因此发布它，以防它可以帮助任何有类似需求的人.

Ok I was able to get it done with the following addition to the query so posting it in case it may help anyone with a similar requirement.

t.status = case when t1.sum_rate >= 23 then 'credit'  else 'cash' end

这篇关于选择总和直到一定数量，然后根据条件更新某些字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！