如何添加组内序号

【问题】

Mysql Table ordered by 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

Column OCCURENCE_COUNTER does not exist in table. I want to make Query, which could tell me about value of OCCURENCE_COUNTER for every row:

// pseudo-code:

foreach(row) {
    if(isFirstOccurenceOfKey(current_key)) {
        current_OCCURENCE_COUNTER = 1;
    } else {
        current_OCCURENCE_COUNTER = previous_OCCURENCE_COUNTER + 1;
    }
}

I want not group OCCURENCE_CONTER by key. I just want to get ungrouped counter.

In this possible to do in one single query (or with some subqueries)?

【回答】

这个用mysql的变量很容易解决，用SPL的ranki函数也很简单实现组内序号：

	A
1	$select id,key from tb order by id
2	=A1.derive(ranki(id;key):OCCURENCE_COUNTER)

A1：sql取数，按照id排序

A2：增加OCCURENCE_COUNTER列，通过ranki(id;key)给相同的key设置序号，结果如下：