如何筛选倒数第二段小数在指定区间内的数据库行

问题背景 #

现有如下表格：

需要筛选出Decimals列中倒数第二段数值处于0.7至0.94（包含两端）区间内的行，示例中仅第二行符合条件。

现有代码问题 #

你当前的SQL写法存在语法错误，LIKE是字符串匹配运算符，不能直接结合数值比较逻辑使用，这种写法无法正确提取并判断倒数第二段的数值。

解决方案 #

核心思路是先提取Decimals中倒数第二段的内容，转换为数值类型后，再判断是否在目标区间内。不同SQL数据库的实现略有差异，以下是主流数据库的写法：

MySQL/MariaDB #

SELECT Rows
FROM your_table
WHERE 
  CAST(
    SUBSTRING_INDEX(SUBSTRING_INDEX(Decimals, '.', -2), '.', 1)
    AS DECIMAL(5,2)
  ) BETWEEN 0.7 AND 0.94;

• 解释：
  1. SUBSTRING_INDEX(Decimals, '.', -2)：提取字符串中最后两个以.分隔的部分（例如16.1.1.1.89.1会得到89.1）
  2. SUBSTRING_INDEX(..., '.', 1)：从上述结果中提取第一个部分，即倒数第二段的内容（89）
  3. CAST(...) AS DECIMAL(5,2)：将提取的字符串转换为小数类型
  4. BETWEEN 0.7 AND 0.94：判断数值是否在目标区间内

SQL Server #

SELECT Rows
FROM your_table
WHERE 
  CAST(
    PARSENAME(Decimals, 2)
    AS DECIMAL(5,2)
  ) BETWEEN 0.7 AND 0.94;

• 解释：PARSENAME函数按.分割字符串，参数2表示取倒数第二段的内容（注意：该函数最多支持4个分段，若Decimals分段数超过4，需先对字符串做拆分处理）

PostgreSQL #

SELECT Rows
FROM your_table
WHERE 
  CAST(
    (STRING_TO_ARRAY(Decimals, '.'))[array_length(STRING_TO_ARRAY(Decimals, '.'), 1) - 1]
    AS DECIMAL(5,2)
  ) BETWEEN 0.7 AND 0.94;

• 解释：
  1. STRING_TO_ARRAY(Decimals, '.')：将字符串按.分割为数组
  2. array_length(..., 1) - 1：获取数组长度减1，即倒数第二段的索引位置
  3. 提取对应索引的元素并转换为小数后，进行区间判断

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