mysql - 可以在排序结果集中按顺序对组进行编号吗？

Question

假设我有下表:

id   | sub_id  | Name
----------------------
1    | 7       | Abe
1    | 90      | Bill
1    | 500     | Tom
2    | 1       | Gilbert
3    | 15      | Jose
3    | 18      | Don

我是否可以选择所有结果并根据id对结果进行顺序编号？ AKA，每次检测到新的 id 时，序列都会从 0 开始。类似于 SQL Server 的 RANK和 DENSE_RANK功能。所以结果将如下所示:

id   | sub_id  | Name     | SequenceNumber
-----------------------------------------
1    | 7       | Abe      |1
1    | 90      | Bill     |2
1    | 500     | Tom      |3
2    | 1       | Gilbert  |1
3    | 15      | Jose     |1
3    | 18      | Don      |2

我可以通过执行以下操作来生成从 1 开始并以最后一行号(上例中的 6)结束的数字序列:

SELECT @row := @row + 1 as row, t.*
FROM my_table t, (SELECT @row := 0) r

但这并不是我真正需要的。有建议吗？

Answer 1

您可以使用另一个变量来存储以前的 id 并将其与新 id 进行比较，为此我使用 case 语句来检查新 id，注意按 id 排序对于分配一个很重要正确的序列号

select t.*,
@row:= case when @group = t.id then @row + 1 else 1 end SequenceNumber,
@group:=t.id
from test t
cross join (SELECT @group:=null, @row := 0) r
order by t.id

Demo

要只包含所需的字段，请参阅下面的演示

Demo 2