MYSQL - 未分组的列值出现次数

Question

Mysql 表按 key asc 排序:

id | key | value | OCCURENCE_COUNTER
--------------------------------------------------------
 1    a     ...          1
 2    a     ...          2
 3    a     ...          3
 4    b     ...          1
 5    b     ...          2
 6    b     ...          3
 7    c     ...          1
 8    c     ...          2
 9    c     ...          3

表中不存在 OCCURENCE_COUNTER 列。我想进行查询，它可以告诉我每一行的 OCCURENCE_COUNTER 值:

// pseudo-code:
foreach(row) {
    if(isFirstOccurenceOfKey(current_key)) {
        current_OCCURENCE_COUNTER = 1;
    } else {
        current_OCCURENCE_COUNTER = previous_OCCURENCE_COUNTER + 1;
    }
}

我不想按 key 对 OCCURENCE_CONTER 进行分组。我只想得到未分组的计数器。

这有可能在一个查询(或一些子查询)中完成吗？

Answer 1

希望我能正确理解您的问题。 occurence_conter 是您在问题中定义的表。

select c.*, 
    ( select count(*) 
        from occurence_conter c1
        where c1.key = c.key
            and c1.id <= c.id ) as OccurenceCounter
from occurence_conter c;