一条update搞定积分兑换的问题

女爷i 2022-07-27 10:44 183阅读 0赞

原型：假设用户的每一笔消费都会生成对应的积分，积分有一定的过期时间，用户兑换商品时需要按日期从小到大的顺序核销每一笔积分，且过期的积分不可用。  
假设：现假设我们积分的过期时间是10天，用户兑换的商品价值80积分，当前时间为**2016-04-12**。  
构造数据：  
（1）创建表：

create table test_point_tl(
    	id int,
    	create_time datetime,
    	point int,
    	used_point int default 0
    );

（2）插入数据：  
假设表中数据的ID与创建时间是同增的。

insert into test_point_tl(id, create_time, point) values(1,'2016-4-1', 10);
    insert into test_point_tl(id, create_time, point) values(2,'2016-4-2', 20);
    insert into test_point_tl(id, create_time, point) values(6,'2016-4-5', 30);
    insert into test_point_tl(id, create_time, point) values(5,'2016-4-4', 15);
    insert into test_point_tl(id, create_time, point) values(4,'2016-4-3', 40);
    insert into test_point_tl(id, create_time, point) values(3,'2016-4-2', 10);

（3）查询原始数据：

select * from test_point_tl order by create_time asc;

![原始数据][20160412114357836]

（4）预期：  
根据上表中的数据，我们核销时应该是从2016-04-02那条记录也就是ID为2的开始核销，最后更新的列应该如下：  
|id|create\_time|point|used\_point|  
|----------|----------|  
|2|2016-04-02|20|20|  
|3|2016-04-02|10|10|  
|4|2016-04-02|40|40|  
|5|2016-04-02|15|10|

（5）执行更新：

update test_point_tl tp1
    set tp1.used_point = case 
    						when tp1.id < @min_id then tp1.point
    						when tp1.id = @min_id then 80 - @min_sum_point + point
    				    end
    where tp1.create_time >= date_sub(current_date(), interval 10 day)
    and tp1.id <= (select min_id
    				from
    					(	select @min_id := min(tl.id) min_id, @min_sum_point:=min(tl.sum_point)
    						from (
    								select tp.*, @value := @value + point-used_point sum_point
    								from test_point_tl tp, (select @value := 0 from dual) r
    								where tp.create_time >= date_sub(current_date(), interval 10 day)
    								order by tp.create_time asc
    								) tl
    						where tl.sum_point >= 80
    					 ) tt
    				 );

（6）查询更新的结果：  
可以看到与我们预期的完全一样。  
![这里写图片描述][20160412115622263]

（7）验证上述更新：  
现在回到原始数据，假设ID为2的记录已经兑换了20个积分，ID为3的记录兑换了8个积分，那么新的原始数据如下：  
![这里写图片描述][20160412115937936]  
执行更新后的结果如下：  
![这里写图片描述][20160412120156603]  
验证：（2+40+15+23）=80，完全正确。

--------------------

欢迎关注我的公众号“彤哥读源码”，查看更多“源码&架构&算法”系列文章, 与彤哥一起畅游源码的海洋。

![qrcode][]

[20160412114357836]: /images/20220724/b3e26daac5d241d19381aba2968b5c62.png
[20160412115622263]: /images/20220724/198d6d212bd047a7868285eea040cd89.png
[20160412115937936]: /images/20220724/deb265213945465b89118a7435301995.png
[20160412120156603]: /images/20220724/695e5c3bcf41487db17bcef82191284c.png
[qrcode]: /images/20220724/8969d06691f941f19ecb2b524464c9ad.png