如何在分区和不排序的情况下使用row_number()

Question

我的表看起来像：

表_1

| Id | Num |
| 1  | 1   |
| 2  | 1   |
| 3  | 1   |
| 4  | 2   |
| 5  | 1   |
| 6  | 2   |
| 7  | 2   |

我希望在"num"列旁边有一个row_number，但只要num更改其值，row_numbers就会重置。

我希望我的桌子看起来像：

| Id | Num | row_num |
| 1  | 1   | 1 |
| 2  | 1   | 2 | 
| 3  | 1   | 3 |
| 4  | 2   | 1 |
| 5  | 1   | 1 |
| 6  | 2   | 1 |
| 7  | 2   | 2 |

Answer 1

这是一种间隙和孤岛问题。对于这个版本，最简单的解决方案可能是使用行号的差异来识别岛屿：

select t.*,
row_number() over (partition by seqnum - seqnum_2 order by id) as row_num
from (select t.*,
row_number() over (order by id) as seqnum,
row_number() over (partition by num order by id) as seqnum_2
from table_1 t
) t;

如果您运行子查询，您将看到差异是如何识别"；相邻的"；CCD_ 1的值。

注意：如果(在您的示例中)id是连续的，没有间隙，您可以将其简化为：

select t.*,
row_number() over (partition by id - seqnum_2 order by id) as row_num
from (select t.*,
row_number() over (partition by num order by id) as seqnum_2
from table_1 t
) t;

Answer 2

使用MySQl 8，您可以执行以下操作：

select Id, Num, Row_Number() over (order by (Select 0)) as row_num from table_1;