SQL中如何筛选列值为正序/倒序连续序列的记录？

你当前的写法仅能匹配固定9位的123456789和987654321两个特定值，无法适配不同长度、不同起始值的连续序列需求，以下是通用的逻辑化实现方案：

核心判断逻辑 #

• 正序连续数字串：任意第n位（n≥2）数字 = 前一位数字 + 1
• 倒序连续数字串：任意第n位（n≥2）数字 = 前一位数字 - 1

常用数据库实现示例 #

MySQL 实现 #

如果仅需要匹配固定9位长度的连续序列，可使用简化写法：

SELECT col 
FROM mytable
WHERE 
  -- 正序连续校验
  col = CONCAT(
    SUBSTRING(col,1,1), 
    SUBSTRING(col,1,1)+1, 
    SUBSTRING(col,1,1)+2, 
    SUBSTRING(col,1,1)+3, 
    SUBSTRING(col,1,1)+4, 
    SUBSTRING(col,1,1)+5, 
    SUBSTRING(col,1,1)+6, 
    SUBSTRING(col,1,1)+7, 
    SUBSTRING(col,1,1)+8
  )
  OR
  -- 倒序连续校验
  col = CONCAT(
    SUBSTRING(col,1,1), 
    SUBSTRING(col,1,1)-1, 
    SUBSTRING(col,1,1)-2, 
    SUBSTRING(col,1,1)-3, 
    SUBSTRING(col,1,1)-4, 
    SUBSTRING(col,1,1)-5, 
    SUBSTRING(col,1,1)-6, 
    SUBSTRING(col,1,1)-7, 
    SUBSTRING(col,1,1)-8
  );

如果需要支持任意长度的连续序列，可使用递归CTE实现逐位校验：

WITH RECURSIVE split_col AS (
  SELECT 
    col,
    1 AS pos,
    CAST(SUBSTRING(col,1,1) AS UNSIGNED) AS prev_num,
    1 AS is_asc,
    1 AS is_desc
  FROM mytable
  UNION ALL
  SELECT 
    sc.col,
    sc.pos + 1,
    CAST(SUBSTRING(sc.col, sc.pos + 1, 1) AS UNSIGNED),
    CASE WHEN CAST(SUBSTRING(sc.col, sc.pos + 1, 1) AS UNSIGNED) = sc.prev_num + 1 THEN sc.is_asc ELSE 0 END,
    CASE WHEN CAST(SUBSTRING(sc.col, sc.pos + 1, 1) AS UNSIGNED) = sc.prev_num - 1 THEN sc.is_desc ELSE 0 END
  FROM split_col sc
  WHERE sc.pos < LENGTH(sc.col)
)
SELECT DISTINCT col 
FROM split_col
WHERE pos = LENGTH(col) AND (is_asc = 1 OR is_desc = 1);

PostgreSQL 实现 #

可以配合序列生成函数简化写法：

SELECT DISTINCT t.col
FROM mytable t
CROSS JOIN LATERAL generate_series(0, length(t.col)-1) i
GROUP BY t.col
HAVING 
  -- 匹配正序
  string_agg((left(t.col,1)::int + i)::text, '' ORDER BY i) = t.col
  OR
  -- 匹配倒序
  string_agg((left(t.col,1)::int - i)::text, '' ORDER BY i) = t.col;

其他数据库的实现逻辑完全一致，仅需替换对应数据库的字符串截取、类型转换相关语法即可。

性能优化建议 #

如果表数据量较大，可参考以下优化方案：

• 给col字段创建普通索引，可大幅提升固定长度场景的查询效率
• 新增预计算标记字段，写入数据时提前判断是否为连续序列，查询时直接筛选标记字段，性能提升最为明显

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