拆分逗号分隔数据关联另一表更新table1.sum的SQL问题

table1存储了逗号分隔的拼接数据，表结构及数据如下：

另有table2存储分值对应关系，结构及数据如下：

需求是更新table1的sum字段：将concats字段中每个逗号分隔的grade对应table2中的score求和，例如id=1的行，b,c对应2.65+2.56=5.21，需填入该行的sum。

用户尝试了以下SQL，但执行报错：

UPDATE table1 as t1 SET sum =      
    (SELECT (CASE WHEN (SELECT SPLIT_PART(concats,',',1) from t1) = t2.grade then t2.score ELSE 0 END) +            
            (CASE WHEN (SELECT SPLIT_PART(concats,',',2) from t1) = t2.grade then t2.score ELSE 0 END) +            
            (CASE WHEN (SELECT SPLIT_PART(concats,',',3) from t1) = t2.grade then t2.score ELSE 0 END) +            
            (CASE WHEN (SELECT SPLIT_PART(concats,',',4) from t1) = t2.grade then t2.score ELSE 0 END) +            
            (CASE WHEN (SELECT SPLIT_PART(concats,',',5) from t1) = t2.grade then t2.score ELSE 0 END) +            
            (CASE WHEN (SELECT SPLIT_PART(concats,',',6) from t1) = t2.grade then t2.score ELSE 0 END) +            
            (CASE WHEN (SELECT SPLIT_PART(concats,',',7) from t1) = t2.grade then t2.score ELSE 0 END ) +            
            (CASE WHEN (SELECT SPLIT_PART(concats,',',8) from t1) = t2.grade then t2.score ELSE 0 END )        
        FROM table2 AS t2 ) 

错误原因 #

这段SQL逻辑存在核心问题：

• 子查询未与主表t1做关联匹配，会返回table2的所有行结果，而UPDATE操作无法接收多行值
• 硬编码拆分位置（1到8）既不灵活，也会对元素数量不符的行产生错误结果

解决方案 #

根据不同数据库环境，提供以下可行方案：

1. PostgreSQL 环境 #

利用string_to_array将字符串转数组，unnest展开后关联table2求和：

WITH split_grades AS (
    SELECT 
        t1.id,
        SUM(t2.score) AS total_score
    FROM table1 t1
    LEFT JOIN table2 t2 ON t2.grade = ANY(string_to_array(t1.concats, ','))
    GROUP BY t1.id
)
UPDATE table1 t1
SET sum = sg.total_score
FROM split_grades sg
WHERE t1.id = sg.id;

2. MySQL 8.0+ 环境 #

方法一：用JSON_TABLE拆分字符串

WITH split_grades AS (
    SELECT 
        t1.id,
        SUM(t2.score) AS total_score
    FROM table1 t1
    JOIN JSON_TABLE(
        CONCAT('["', REPLACE(t1.concats, ',', '","'), '"]'),
        '$[*]' COLUMNS(grade VARCHAR(10) PATH '$')
    ) jt
    LEFT JOIN table2 t2 ON t2.grade = jt.grade
    GROUP BY t1.id
)
UPDATE table1 t1
JOIN split_grades sg ON t1.id = sg.id
SET t1.sum = sg.total_score;

方法二：递归CTE拆分字符串

WITH RECURSIVE split_grades AS (
    SELECT 
        id,
        concats,
        1 AS pos,
        SUBSTRING_INDEX(concats, ',', 1) AS grade,
        SUBSTRING(concats, LENGTH(SUBSTRING_INDEX(concats, ',', 1)) + 2) AS remaining
    FROM table1
    WHERE concats IS NOT NULL AND concats != ''
    UNION ALL
    SELECT 
        id,
        concats,
        pos + 1,
        SUBSTRING_INDEX(remaining, ',', 1) AS grade,
        SUBSTRING(remaining, LENGTH(SUBSTRING_INDEX(remaining, ',', 1)) + 2) AS remaining
    FROM split_grades
    WHERE remaining IS NOT NULL AND remaining != ''
)
UPDATE table1 t1
JOIN (
    SELECT id, SUM(t2.score) AS total_score
    FROM split_grades sg
    LEFT JOIN table2 t2 ON t2.grade = sg.grade
    GROUP BY id
) sg ON t1.id = sg.id
SET t1.sum = sg.total_score;

3. MySQL 5.x 环境 #

先创建字符串拆分函数：

DELIMITER //
CREATE FUNCTION split_str(x VARCHAR(255), delim VARCHAR(12), pos INT)
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
    RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');
END//
DELIMITER ;

再执行更新：

UPDATE table1 t1
SET sum = (
    SELECT SUM(t2.score)
    FROM (
        SELECT split_str(t1.concats, ',', 1) AS grade UNION ALL
        SELECT split_str(t1.concats, ',', 2) UNION ALL
        SELECT split_str(t1.concats, ',', 3) UNION ALL
        SELECT split_str(t1.concats, ',', 4) UNION ALL
        SELECT split_str(t1.concats, ',', 5) UNION ALL
        SELECT split_str(t1.concats, ',', 6) UNION ALL
        SELECT split_str(t1.concats, ',', 7) UNION ALL
        SELECT split_str(t1.concats, ',', 8)
    ) grades
    LEFT JOIN table2 t2 ON t2.grade = grades.grade
    WHERE grades.grade IS NOT NULL AND grades.grade != ''
);

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