如何在Oracle SQL中使用单列多个子串筛选表行并按规则取目标行

问题原因 #

你的原有SQL存在两个核心问题：

1. 子查询计算的是ID-1为1001/1002/1003的所有行的全局最大ID-2，而非每个ID-1各自分组的最大ID-2，因此只会返回全局ID-2最高的行，无法为每个ID-1单独取最大值。
2. 未处理Case列长度不足9位时ID-2默认00的规则：SUBSTR取超出字符串长度的位置时会返回空值，和你要求的默认逻辑不符。

解决方案 #

使用Oracle的窗口函数ROW_NUMBER()实现分组排序取Top1，无需修改表结构，完全兼容只读数据库场景：

SELECT *
FROM (
    SELECT 
        t.*,
        ROW_NUMBER() OVER (
            PARTITION BY SUBSTR("Case", 3, 4) -- 按ID-1分组
            ORDER BY 
                NVL(SUBSTR("Case", 8, 2), '00') DESC, -- 组内优先按ID-2降序，空值默认'00'
                Sequence DESC -- ID-2相同的情况下按ID-3降序
        ) AS rn
    FROM your_table_name t
    WHERE SUBSTR("Case", 3, 4) IN ('1001', '1002', '1003')
)
WHERE rn = 1;

注意事项 #

如果Sequence列是字符串存储的数值，为了避免字典序排序错误（比如字符串'99'字典序大于'100'），可以将排序规则修改为：

ORDER BY NVL(SUBSTR("Case", 8, 2), '00') DESC, TO_NUMBER(Sequence) DESC

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