本文介绍了MySQL - 使用增量串联转换唯一的重复值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
就在之前，我发布了一个关于获取每个计数器的问题列中的重复值
Just before, I posted a question about getting a counter for each duplicate values in column
现在我将扩展这个问题，例如:
Now I'll extend this question such as:
+------+------+------+
| id | data | num |
+------+------+------+
| 1 | a | |
| 2 | a | |
| 3 | a | |
| 4 | b | |
| 5 | b | |
| 6 | c | |
| 7 | d | |
| 8 | a | |
| 9 | b | |
+------+------+------+
可以更新为:
+------+------+------+
| id | data | num |
+------+------+------+
| 1 | a | a1 |
| 2 | a | a2 |
| 3 | a | a3 |
| 4 | b | b1 |
| 5 | b | b2 |
| 6 | c | c1 |
| 7 | d | d1 |
| 8 | a | a4 |
| 9 | b | b3 |
+------+------+------+
无论如何都要向 bluefeet 鞠躬！
A big bow to bluefeet anyway!
推荐答案
SOLVED:
UPDATE mytable
SET num = ( SELECT num FROM
(
select id,
concat(data, cast(group_row_number as char)) as num
from
(
select id, data,
@num := if(@data = `data`, @num + 1, 1) as group_row_number,
@data := `data` as dummy
from
(
select id, data
from mytable, (SELECT @rn:=0) r
) x
order by data, id
) x order by id
) tempSort
WHERE mytable.ID = tempSort.ID );
再次感谢@bluefeet！！
Thks again @bluefeet!!
这篇关于MySQL - 使用增量串联转换唯一的重复值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！