这是我表的当前状态。
id operation position
------------------------
1 EDIT 0
1 DELETE 0
2 VIEW 0
3 DELETE 0
3 VIEW 0
3 EDIT 0
我想更新mysql中的位置值,说是否将id = 1
第一个条目设置为0秒,且同一个ID递增1,所以我的最终输出应该像
id operation position
------------------------
1 EDIT 0
1 DELETE 1
2 VIEW 0
3 DELETE 0
3 VIEW 1
3 EDIT 2
任何提示,我怎么能做到这一点?
set @prev_id = 0;
set @count = 0;
update actions inner join
(select @count := IF(@prev_id = id, @count + 1, 0) as count, @prev_id := id as prev_id, operation
from actions
order by id) as updated
on actions.id = updated.prev_id and actions.operation = updated.operation
set actions.position = updated.count
高温超导
编辑:我命名表actions
。另外,由于没有可唯一标识记录的列,因此我使用了id
和的组合operation
。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句