获取表列中序列值的范围

Question

我的列中有一个值列表。并且要查询范围。例如，如果值为1,2,3,4,5,9,11,12,13,14,17,18,19

我想显示1-5,9,11-14,17-19

Answer 1

假设每个值都存储在一个单独的行中，您可以在这里使用一些间隙和孤岛技术：

select case when min(val) <> max(val)
then concat(min(val), '-', max(val))
else min(val)
end val_range
from (select val, row_number() over(order by val) rn from mytable) t
group by val - rn
order by min(val)

这个想法是通过取值和递增秩之间的差来构建连续值的组，递增秩是使用row_number()(在MySQL 8.0中可用(计算的：

DB Fiddle上的演示：

|val_range||：--------||1-5||9||11-14||17-19|

在早期版本中，可以使用相关的子查询或用户变量模拟row_number()。第二种选择是：

select case when min(val) <> max(val)
then concat(min(val), '-', max(val))
else min(val)
end val_range
from (select @rn := 0) x
cross join (
select val, @rn := @rn + 1 rn 
from (select val from mytable order by val) t
) t
group by val - rn
order by min(val)

Answer 2

作为其他答案的补充：

select dn.val as dnval, min(up.val) as upval 
from mytable up
join mytable dn
on dn.val <= up.val
where not exists (select 1 from mytable a where a.val = up.val + 1)
and not exists (select 1 from mytable b where b.val = dn.val - 1)
group by dn.val
order by dn.val;
1   5
9   9
11  14
17  19

不用说，但使用像@GNB这样的OLAP函数的效率要高出几个数量级。

一篇关于如何在MySQL中模拟OLAP函数的短文<8可以在以下位置找到：

mysql-row_number

Fiddle

编辑：

如果引入另一个维度(在本例中为p(，则类似于：

select dn.p, dn.val as dnval, min(up.val) as upval 
from mytable up
join mytable dn
on dn.val <= up.val
and dn.p = up.p
where not exists (select 1 from mytable a where a.val = up.val + 1 and a.p = up.p)
and not exists (select 1 from mytable b where b.val = dn.val - 1 and b.p = dn.p)
group by dn.p, dn.val
order by dn.p, dn.val;

可以使用，请参阅Fiddle2