mysql列中的组序列值

Question

表mytbl有两列：col_1&col2。我想将col_1中的值范围分组为单个col_2值。

示例：

col_1	col_2
1	3
2	1
3	3
4	3
5	2
7	3
8	3
9	3
10

这是一个缺口和孤岛问题。以下是一种使用行号之间的差异来识别组的方法：

select 
min(col_1) as start_col_1, 
case when max(col_1) <> min(col_1) then max(col_1) end as end_col_1, 
col2
from (
select t.*,
row_number() over(partition by col2 order by col_1) as rn
from mytable t
) t
where col_2 = 3
group by col2, col_1 - rn
order by start_col1

当岛仅由一个记录组成时，这将返回null而不是'-'(这是因为后者不是有效数字(。

只要col_1在没有间隙的情况下递增，这就起作用。否则，我们可以用另一个row_number():生成我们自己的序列

select 
min(col_1) as start_col_1, 
case when max(col_1) <> min(col_1) then max(col_1) end as end_col_1, 
col2
from (
select t.*,
row_number() over(order by col_1) as rn1,
row_number() over(partition by col2 order by col_1) as rn2
from mytable t
) t
where col_2 = 3
group by col2, rn1 - rn2
order by start_col1