mysql - 如何使用索引更新重复的行(Mysql)

Question

我有一张名为 city 的表。

city_name
---------------
New York
Beijing
New York
New York
Dubai
Beijing
---------------

更新后，我希望它是:

city_name,   index
---------------
New York,    0
Beijing,     0
New York,    1
New York,    2
Dubai,       0
Beijing,     1
---------------

模式是这样的:第一个纽约的索引为 0，第二个纽约的索引为 1，第三个为 2。此表中有数百万行。

有什么简单的方法可以进行此更新吗？

我想分两步解决这个问题。

第一步:

@cities = Select distinct city_name from city;

第二步:

foreach @cities as @city
   update city set index = row_num where city_name = @city.cityname

在mysql中好像没有row_num。

Answer 1

试试这个:

update city cross join
       (select @city := '', @prevcity := '', @i := 0) const
    set `index` = (case when (@prevcity := @city) is null then null
                        when (@city := city) is null then null
                        else @i := if(@prevcity = city, @i + 1, 1) is null then null
                   end)
    order by city; 

如果您熟悉在 select 语句中使用变量进行枚举，那么这也是类似的。复杂的是确保 update 的评估顺序。这是通过使用 case 语句来处理的，该语句按顺序评估每个子句，直到有一个为真。前两个保证为假(因为值永远不应该是 NULL)。

编辑:

如果您有一个唯一的 ID，那么解决方案会更容易一些。我希望你能做到这一点:

update city c
    set `index` = (select count(*) from city c2 where c2.city = c.city and c2.id <= c.id);

但是，您可以通过更多连接来实现:

update city c join
       (select id, (select count(*) from city c2 where c2.city = c1.city and c2.id <= c1.id) as newind
        from city c1
       ) ci
       on c.id = ci.id
    set c.`index` = ci.newind;