MySQL - 使用增量串联转换唯一的重复值 [英] MySQL - Convert a duplicate value in an unique with an increment concatenation

问题描述

就在之前，我发布了一个关于获取每个计数器的问题列中的重复值

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 - 使用增量串联转换唯一的重复值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！