如何使用SQL从带间隔的数值列中提取各连续数值区间

这是典型的SQL「岛屿问题」（连续序列分组需求），以下是通用实现方案： #

前置假设 #

我们假定存储待处理数据的表名为num_table，数值列名为val，列中包含你给出的带null、有间隔的样例数据。 #

通用实现（兼容MySQL8.0+、PostgreSQL、SQL Server、Oracle等支持窗口函数的数据库） #

WITH filtered_data AS (
    -- 过滤空值，同时给每行生成连续序号
    SELECT 
        val,
        ROW_NUMBER() OVER (ORDER BY val) AS rn
    FROM num_table
    WHERE val IS NOT NULL
)
-- 按差值分组，取每组最小/最大值即为连续区间首尾
SELECT
    MIN(val) AS 区间起始值,
    MAX(val) AS 区间结束值,
    CONCAT('区间', ROW_NUMBER() OVER (ORDER BY MIN(val)), ' - ', MIN(val), ' 至 ', MAX(val)) AS 区间展示文本
FROM filtered_data
GROUP BY (val - rn)
ORDER BY 区间起始值;

实现原理 #

核心逻辑利用了连续数值与连续行号的差值固定的特性：比如81、82、83对应的行号为1、2、3，val - rn的结果均为80，会被分到同一组；下一段连续序列的第一个值86对应的行号为4，86-4=82，和前一组差值不同，自动进入新分组。 #

低版本MySQL实现（不支持窗口函数场景） #

如果使用的是MySQL5.x等不支持CTE和窗口函数的版本，可以用变量替代实现：

SELECT 
    MIN(val) AS 区间起始值,
    MAX(val) AS 区间结束值,
    CONCAT('区间', @group_idx := @group_idx + 1, ' - ', MIN(val), ' 至 ', MAX(val)) AS 区间展示文本
FROM (
    SELECT 
        val,
        @rn := @rn + 1,
        val - @rn AS group_flag
    FROM num_table, (SELECT @rn := 0, @group_idx := 0) AS init
    WHERE val IS NOT NULL
    ORDER BY val
) AS t
GROUP BY group_flag
ORDER BY 区间起始值;

运行结果 #

和你给出的预期输出完全一致：

内容的提问来源于stack exchange，提问作者dso