SQL按分隔符解析字符串，按颜色分组求和不同位置数值

错误原因 #

1. 聚合函数位置错误：你把sum()放在了CASE分支内部，不符合SQL聚合逻辑——应该先通过CASE判断每条记录对应要累加的数值，再对这些数值做SUM聚合。
2. 数据类型转换错误：用to_char()包裹需要求和的数值字段，导致SUM操作时需隐式将字符串转数字，当字符串无法转成有效数字时就会抛出「invalid number」错误。
3. 代码冗余：重复编写大量字符串截取逻辑，既不便于维护，也容易出错。

正确实现方案 #

先通过CTE（公共表表达式）提取每条目标记录的颜色和对应要累加的数值，再分组求和，逻辑更清晰：

WITH parsed_data AS (
    SELECT
        -- 提取第2个分隔后的字段作为颜色
        TRIM(SUBSTR(t.data, INSTR(t.data, '^', 1, 1) + 1, INSTR(t.data, '^', 1, 2) - INSTR(t.data, '^', 1, 1) - 1)) AS COLOR,
        -- 根据颜色提取对应位置的数值并转为数字类型
        CASE
            WHEN TRIM(SUBSTR(t.data, INSTR(t.data, '^', 1, 1) + 1, INSTR(t.data, '^', 1, 2) - INSTR(t.data, '^', 1, 1) - 1)) = 'Blue'
                THEN TO_NUMBER(SUBSTR(t.data, INSTR(t.data, '^', 1, 3) + 1, INSTR(t.data, '^', 1, 4) - INSTR(t.data, '^', 1, 3) - 1))
            WHEN TRIM(SUBSTR(t.data, INSTR(t.data, '^', 1, 1) + 1, INSTR(t.data, '^', 1, 2) - INSTR(t.data, '^', 1, 1) - 1)) = 'Red'
                THEN TO_NUMBER(SUBSTR(t.data, INSTR(t.data, '^', 1, 5) + 1, INSTR(t.data, '^', 1, 6) - INSTR(t.data, '^', 1, 5) - 1))
            WHEN TRIM(SUBSTR(t.data, INSTR(t.data, '^', 1, 1) + 1, INSTR(t.data, '^', 1, 2) - INSTR(t.data, '^', 1, 1) - 1)) = 'Green'
                THEN TO_NUMBER(SUBSTR(t.data, INSTR(t.data, '^', 1, 4) + 1, INSTR(t.data, '^', 1, 5) - INSTR(t.data, '^', 1, 4) - 1))
            ELSE 0 -- 非目标颜色返回0，不影响求和结果
        END AS QTY_VALUE
    FROM table t
    WHERE t.data LIKE '%^220UU^%'
)
SELECT
    COLOR,
    SUM(QTY_VALUE) AS QTY
FROM parsed_data
WHERE COLOR IN ('Blue', 'Red', 'Green') -- 仅保留目标颜色
GROUP BY COLOR
ORDER BY COLOR;

关键优化点 #

• 用CTEparsed_data提前解析每条记录的核心数据，简化后续分组逻辑
• 用TO_NUMBER()明确转换数值类型，避免隐式转换引发的错误
• 将SUM放在外层对CASE返回的数值做聚合，符合SQL语法规则
• 增加TRIM()处理可能的空格，避免颜色匹配失败
• 新增过滤条件确保结果仅包含目标颜色

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