Oracle SQL中带下划线的字符串数字如何正确获取MAX/MIN值

针对字符串格式（如123456_1、123456_13）无法按数值逻辑取MAX/MIN的问题，可通过拆分下划线前后的数字部分，按数值排序后获取原始格式的结果，以下是几种实用方案：

方案1：直接排序取单条极值 #

适用于只需要获取MAX或MIN的场景，拆分字符串为前后两段数字，按数值降序/升序排序后取第一条：

获取最大值 #

SELECT number_col
FROM your_table
ORDER BY 
  -- 提取下划线前的部分转为数字，降序排序
  TO_NUMBER(SUBSTR(number_col, 1, INSTR(number_col, '_') - 1)) DESC,
  -- 提取下划线后的部分转为数字，降序排序
  TO_NUMBER(SUBSTR(number_col, INSTR(number_col, '_') + 1)) DESC
-- 仅取第一条结果
FETCH FIRST 1 ROW ONLY;

获取最小值 #

只需将排序规则改为升序：

SELECT number_col
FROM your_table
ORDER BY 
  TO_NUMBER(SUBSTR(number_col, 1, INSTR(number_col, '_') - 1)) ASC,
  TO_NUMBER(SUBSTR(number_col, INSTR(number_col, '_') + 1)) ASC
FETCH FIRST 1 ROW ONLY;

方案2：一次获取MAX和MIN值 #

使用分析函数ROW_NUMBER()标记排序后的位置，再通过聚合函数一次性提取两个极值：

WITH ranked_data AS (
  SELECT 
    number_col,
    -- 标记最大值的排序位置
    ROW_NUMBER() OVER (ORDER BY 
      TO_NUMBER(SUBSTR(number_col, 1, INSTR(number_col, '_') - 1)) DESC,
      TO_NUMBER(SUBSTR(number_col, INSTR(number_col, '_') + 1)) DESC) AS rn_max,
    -- 标记最小值的排序位置
    ROW_NUMBER() OVER (ORDER BY 
      TO_NUMBER(SUBSTR(number_col, 1, INSTR(number_col, '_') - 1)) ASC,
      TO_NUMBER(SUBSTR(number_col, INSTR(number_col, '_') + 1)) ASC) AS rn_min
  FROM your_table
)
SELECT 
  MAX(CASE WHEN rn_max = 1 THEN number_col END) AS max_nr,
  MAX(CASE WHEN rn_min = 1 THEN number_col END) AS min_nr
FROM ranked_data;

方案3：正则表达式简化拆分 #

如果下划线前后的字符长度不固定，用正则表达式提取更简洁：

-- 获取最大值
SELECT number_col
FROM your_table
ORDER BY 
  TO_NUMBER(REGEXP_SUBSTR(number_col, '^[^_]+')) DESC,
  TO_NUMBER(REGEXP_SUBSTR(number_col, '[^_]+$')) DESC
FETCH FIRST 1 ROW ONLY;

以上方案都会保留原始的带下划线格式，且按数值逻辑正确识别极值，比如你的示例数据会返回123456_13作为最大值，123456_1作为最小值。

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