Oracle SQL查询：按VERSION_ID前缀分组取最小ID的唯一结果

原始表数据 #

需求说明 #

筛选VERSION_ID匹配ABC%.%格式的记录，按VERSION_ID中.分隔的前缀（例如ABC1234）分组，保留每组中ID最小的条目。

实现SQL语句 #

SELECT ID, VERSION_ID
FROM (
    SELECT 
        ID, 
        VERSION_ID,
        ROW_NUMBER() OVER (
            PARTITION BY SUBSTR(VERSION_ID, 1, INSTR(VERSION_ID, '.') - 1)
            ORDER BY ID ASC
        ) AS rn
    FROM your_table_name -- 替换为实际表名
    WHERE VERSION_ID LIKE 'ABC%.%'
) t
WHERE rn = 1;

语句说明 #

1. 筛选条件：通过WHERE VERSION_ID LIKE 'ABC%.%'过滤出符合格式的目标记录；
2. 分组依据：用SUBSTR(VERSION_ID, 1, INSTR(VERSION_ID, '.') - 1)截取VERSION_ID中.之前的前缀作为分组字段；
3. 取最小ID：借助ROW_NUMBER()窗口函数，在每个分组内按ID升序排序并标记行号，最后筛选行号为1的记录，即为每组ID最小的条目。

补充：如果版本号格式固定（仅含一个.），也可以用正则表达式函数简化前缀截取：

PARTITION BY REGEXP_SUBSTR(VERSION_ID, '^[^.]+')

执行结果 #

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