如何根据MySQL中的组添加自动增量ID [英] How to add auto increment id according to a group in mysql

问题描述

这是表格的格式:

indexer group name id
1       abc   a
2       abc   b
3       xyz   c
4       abc   e
5       xyz   d

现在我希望它像

indexer group name id
1       abc   a    1
2       abc   b    2
3       xyz   c    1
4       abc   e    3
5       xyz   d    2

"id"应根据组"自动递增

"id" should auto increment according to "group"

推荐答案

尝试一下:

update yourtable t1
join (
    select
          tt.indexer, @rowno := if(@grp = `group`, @rowno + 1, 1) as id, @grp := `group`
    from (select * from yourtable order by `group`, indexer) tt
    cross join (select @rowno := 0, @grp := null) t
) t2
on t1.indexer = t2.indexer
set t1.id = t2.id

此处演示

已

Edited:

如果要插入新行，则必须这样做:

If you want to insert a new row, you have to do it like this:

insert into yourtable
select '$indexer', '$group', '$name', coalesce(max(id), 0) + 1
from yourtable
where name = '$name'

这篇关于如何根据MySQL中的组添加自动增量ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！