SQL分组取最新日期获取唯一记录及子串长度为负报错解决方法

问题原因 #

1. 报错触发原因：你使用instr(ID,'-',7)-1作为substr的长度参数时，若ID长度不足7位、或者从第7位开始不存在'-'，instr会返回0，计算得到的长度为-1，触发Negative sub string length not allowed报错。
2. 逻辑错误1：子查询中仅定义了EST_ID字段，未返回ID字段，外层查询引用ll.ID属于非法字段引用。
3. 逻辑错误2：外层GROUP BY包含了ll.EST_DT、ll.EST_CLAIM_DT等日期字段，相同ID前缀的不同日期记录会被拆分为不同分组，最终产生重复记录。

解决方案 #

推荐使用窗口函数替代嵌套子查询+GROUP BY的写法，逻辑更清晰，同时避免字符串截取报错：

方案1（适用支持SPLIT_PART的数据库，如PostgreSQL、Redshift、Snowflake等）

直接按分隔符拆分ID，完全避免长度计算错误：

WITH ranked_records AS (
    SELECT
        -- 拆分ID取第4段，对应你需要的1040452格式的ID
        SPLIT_PART(ID, '-', 4) AS ID,
        est_date,
        est_claimed_dt,
        col1,
        col2,
        -- 按提取的ID分组，组内按日期倒序排序，最新的记录排在第一位
        ROW_NUMBER() OVER (
            PARTITION BY SPLIT_PART(ID, '-', 4)
            ORDER BY TO_DATE(est_date, 'DD/MM/YYYY') DESC
        ) AS rn
    FROM 你的实际表名
)
SELECT ID, est_date, est_claimed_dt, col1, col2
FROM ranked_records
WHERE rn = 1;

方案2（通用兼容所有支持窗口函数的数据库）

如果你的数据库不支持SPLIT_PART，用更稳妥的字符串截取方式，先过滤不符合格式的ID避免负数长度：

WITH preprocessed AS (
    SELECT
        ID,
        est_date,
        est_claimed_dt,
        col1,
        col2,
        -- 先计算第三、第四个'-'的位置
        INSTR(ID, '-', 1, 3) AS third_dash_pos,
        INSTR(ID, '-', 1, 4) AS fourth_dash_pos
    FROM 你的实际表名
    -- 过滤不符合ID格式的记录，避免后续截取报错
    WHERE INSTR(ID, '-', 1, 4) > 0
),
ranked_records AS (
    SELECT
        SUBSTR(ID, third_dash_pos + 1, fourth_dash_pos - third_dash_pos - 1) AS ID,
        est_date,
        est_claimed_dt,
        col1,
        col2,
        ROW_NUMBER() OVER (
            PARTITION BY SUBSTR(ID, third_dash_pos + 1, fourth_dash_pos - third_dash_pos - 1)
            ORDER BY TO_DATE(est_date, 'DD/MM/YYYY') DESC
        ) AS rn
    FROM preprocessed
)
SELECT ID, est_date, est_claimed_dt, col1, col2
FROM ranked_records
WHERE rn = 1;

以上两种方案都会按你需要提取的ID分组，每组仅返回日期最新的一条记录，符合你的预期输出要求，同时从根源上避免了substr长度为负的报错。

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