SQL实现CustomerContent表按规则拆分映射插入CustomerContentTemp表

• 首先提取ProductContent字段中去掉公共前缀My product is:后的剩余内容
• 对剩余内容按冒号:进行拆分，拆分后对每个子串去除前后空白字符得到独立产品项
• 每个产品项按照给定映射规则赋值，无匹配项赋值为0
• 将处理后的CustomerId和对应Values插入目标表CustomerContentTemp

MySQL 8.0+版本实现（支持递归CTE拆分字符串） #

INSERT INTO CustomerContentTemp (CustomerId, `Values`)
WITH RECURSIVE split_cte AS (
    -- 基础层：提取前缀后的待拆分内容
    SELECT 
        CustomerId,
        TRIM(SUBSTRING(ProductContent, LENGTH('My product is:') + 1)) AS remain_content,
        1 AS level
    FROM CustomerContent
    UNION ALL
    -- 递归层：逐个拆分冒号分隔的产品项
    SELECT 
        CustomerId,
        TRIM(SUBSTRING(remain_content, INSTR(remain_content, ':') + 1)) AS remain_content,
        level + 1
    FROM split_cte
    WHERE INSTR(remain_content, ':') > 0
),
product_cte AS (
    -- 提取所有独立产品项
    SELECT 
        CustomerId,
        CASE 
            WHEN INSTR(remain_content, ':') > 0 THEN TRIM(SUBSTRING(remain_content, 1, INSTR(remain_content, ':') - 1))
            ELSE remain_content 
        END AS product_item
    FROM split_cte
)
-- 按映射规则赋值
SELECT 
    CustomerId,
    CASE product_item
        WHEN 'Shoes' THEN 1
        WHEN 'Diabetic Shoes' THEN 1
        WHEN 'Shoes Back Brace' THEN 8
        WHEN 'Dexcom G6 (CGM)' THEN 37
        WHEN 'Freestyle Libre (CGM)' THEN 37
        WHEN 'Shoes Knee Brace' THEN 14
        ELSE 0
    END AS `Values`
FROM product_cte
ORDER BY CustomerId;

PostgreSQL版本实现（内置拆分函数更简洁） #

INSERT INTO CustomerContentTemp (CustomerId, "Values")
SELECT 
    CustomerId,
    CASE trim(product_item)
        WHEN 'Shoes' THEN 1
        WHEN 'Diabetic Shoes' THEN 1
        WHEN 'Shoes Back Brace' THEN 8
        WHEN 'Dexcom G6 (CGM)' THEN 37
        WHEN 'Freestyle Libre (CGM)' THEN 37
        WHEN 'Shoes Knee Brace' THEN 14
        ELSE 0
    END AS "Values"
FROM CustomerContent,
     unnest(string_to_array(substr(ProductContent, length('My product is:') + 1), ':')) AS product_item
ORDER BY CustomerId;

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