选择总和直到一定数量,然后根据条件更新某些字段 [英] 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屋!