如何添加组内序号
【问题】
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设置序号，结果如下：
