使用基于字段重置的递增值更新字段 [英] Update a field with an incrementing value that resets based on field

问题描述

我在 MySQL 中有下表:

I have the following table in MySQL:

|id|user|line|
|10|0001|    |
|11|0001|    |
|12|0002|    |
|13|0003|    |
|14|0003|    |
|15|0003|    |

我需要将行列更新为为每个用户重置的递增数字，所以我最终会得到这个:

I need to update the line column to be an incrementing number that resets for each user so I woudl end up with this:

|id|user|line|
|10|0001|   1|
|11|0001|   2|
|12|0002|   1|
|13|0003|   1|
|14|0003|   2|
|15|0003|   3|

我过去在 MS SQL 中使用过 ROW_NUMBER() OVER (PARTITION...).有没有办法在 MySQL 中做到这一点?

I've used ROW_NUMBER() OVER (PARTITION... for this in MS SQL in the past. Is there a way to do this in MySQL?

推荐答案

MySQL 解决方案:

update 
  table_name t,
  ( select 
        id, user, line
      , case when @prev=(@curr:=user)
             then @row_num:=(@row_num + 1)
             else @row_num:=1
        end as unique_user_row_num
      , @prev:=@curr as temp_curr
    from table_name,
         (select @prev:='', @curr:='', @row_num:=0) row_nums
  ) d
set 
  t.line = d.unique_user_row_num
where 
  t.id=d.id
;

演示@MySQL 5.5.32 Fiddle

这篇关于使用基于字段重置的递增值更新字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！