Oracle SQL解决ORA-01489错误：排除行数≥3的ID并拼接消息

问题说明 #

现有表结构如下：

需要将同一ID的Message字段拼接为单个字段，但部分ID（如ID 1）记录数过多，拼接后字符长度超过4000，触发Oracle的ORA-01489错误。要求排除所有记录数≥3的ID，最终期望输出：

原代码存在语法错误，且未实现多行ID的排除逻辑，需在SELECT语句内完成解决方案。

解决方案 #

通过窗口函数COUNT(*) OVER (PARTITION BY ID)先统计每个ID的记录数，筛选出记录数<3的ID后，再进行LISTAGG拼接操作。完整SQL如下：

SELECT 
    id,
    LISTAGG(CASE WHEN MESSAGE = 'X' THEN 'Z' ELSE MESSAGE END, ',') WITHIN GROUP (ORDER BY Message) AS Message,
    Time,
    Zone
FROM (
    SELECT 
        *,
        COUNT(*) OVER (PARTITION BY ID) AS id_count
    FROM TABLE
) t
WHERE t.id_count < 3
GROUP BY id, Time, Zone;

关键说明 #

• 窗口函数统计行数：内层子查询通过COUNT(*) OVER (PARTITION BY ID)计算每个ID对应的记录数，生成临时列id_count
• 筛选符合条件的ID：外层查询通过WHERE t.id_count < 3排除记录数≥3的ID，从根源避免拼接过长触发错误
• 修正LISTAGG语法：补充LISTAGG的分隔符、WITHIN GROUP排序子句，闭合原代码中未完成的CASE语句，最后通过GROUP BY按ID、Time、Zone分组完成拼接

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