如何用SQL查询筛选出相邻差值大于等于指定值的所有数值

数据老陈

2026-9-26

SQL 递推差值规则筛选数据方案

前提说明

测试表名为num_table，存储数值的字段为number
自定义差值阈值a = 3
筛选规则：以排序后的第一个数值为初始基准，每次向后查找第一个与基准差值≥a的数值作为新基准，循环迭代直到遍历完所有数据

实现代码（支持递归CTE的数据库，如MySQL8.0+、PostgreSQL、SQL Server）

-- 定义阈值变量，不同数据库变量定义语法可对应调整
SET @a = 3;

WITH RECURSIVE filtered_result AS (
    -- 锚点：取最小的第一个数值作为初始基准
    SELECT number, number AS last_benchmark, 1 AS rank
    FROM num_table
    ORDER BY number ASC
    LIMIT 1

    UNION ALL

    -- 递归迭代：以上一个基准为起点找下一个符合条件的数值
    SELECT n.number, n.number AS last_benchmark, f.rank + 1
    FROM filtered_result f
    INNER JOIN num_table n ON n.number >= f.last_benchmark + @a
    ORDER BY n.number ASC
    LIMIT 1
)
SELECT number FROM filtered_result ORDER BY rank ASC;

低版本MySQL兼容实现（无递归CTE场景）

SET @a = 3;
SET @last_benchmark = -999999; -- 初始值设为远小于表中最小数值即可

SELECT number FROM (
    SELECT
        number,
        CASE WHEN number >= @last_benchmark + @a THEN @last_benchmark := number ELSE NULL END AS hit_flag
    FROM num_table
    ORDER BY number ASC
) temp
WHERE hit_flag IS NOT NULL;