mysql - 如何赋予重复的唯一编号？

Question

我有一个像这样的表 ID

52
52
53
54
54
54
55

我需要找到重复的内容并像这样更新它们

52   1
52   2
53   1
54   1
54   2
54   3
55   1

此查询

SELECT klient_id,
  (
    SELECT COUNT(klient_id)
    FROM vClient_priklucok
    WHERE klient_id = data.klient_id
  ) AS count
FROM vClient_priklucok AS data

返回这样的数据

52   2
52   2
53   1
54   3
54   3
54   3
55   1

提前致谢

Answer 1

SELECT id,         
       @rownum := case when @prevId <> id then 1 else @rownum + 1 end as rownumber,
       @prevId := id
FROM your_table
CROSS JOIN (SELECT @rownum := 0, @prevId := 0) r
ORDER BY id ASC