从列表中获取所有顺序块 [英] Get all sequential block from a list

问题描述

我在 mysql 中有一个这样的数字列表

i have a list of number in mysql like that

column 1       column2            column 3
4 
6
7 
88
21
29
30 
31

我怎样才能得到所有的连续块，结果应该是

How can i get all sequential blocks, result should be

6
7
29
30
31

推荐答案

这是使用自联接和 union 的一种方法.

This is one way to it using self-join and union.

select t1.val
from t t1
join t t2 on t1.val = t2.val-1
union
select t2.val
from t t1
join t t2 on t1.val = t2.val-1
order by 1

我意识到这可以通过单个查询来完成，而不是使用 union.

I realized this could be done with a single query instead of using union.

select distinct t1.val
from t t1
join t t2 on t1.val = t2.val-1 or t1.val = t2.val+1
order by 1

这篇关于从列表中获取所有顺序块的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！