mysql - MySQL表列更新为基于另一列的顺序数字

Question

当前表看起来像这样:

[id | section | order | thing]
[1  | fruits  | 0     | apple]
[2  | fruits  | 0     | banana]
[3  | fruits  | 0     | avocado]
[4  | veggies | 0     | tomato]
[5  | veggies | 0     | potato]
[6  | veggies | 0     | spinach]

我想知道如何让表格看起来更像这样:

[id | section | order | thing]
[1  | fruits  | 1     | apple]
[2  | fruits  | 2     | banana]
[3  | fruits  | 3     | avocado]
[4  | veggies | 1     | tomato]
[5  | veggies | 2     | potato]
[6  | veggies | 3     | spinach]

根据“section”列和“id”列，“order”列更新为从 1 开始的序号。

Answer 1

您可以使用 join 通过 update 执行此操作。连接的第二个表计算顺序，然后用于更新:

update t join
       (select t.*, @rn := if(@prev = t.section, @rn + 1, 1) as rn
        from t cross join (select @rn := 0, @prev := '') const
       ) tsum
       on t.id = tsum.id
    set t.ordering = tsum.rn