MariaDB 10.3环境下用数组循环实现行转列批量更新的方法咨询

一、先修正你现有语法错误的INSERT语句 #

你写的SQL存在语法顺序错误，JOIN子句必须放在FROM之后、GROUP BY之前，同时字段标识符不要用单引号，INSERT的列要和SELECT返回列数量对应，修正后的写法如下：

INSERT INTO results (`pID`, `series`, `wood`, `language`)
SELECT 
    table1.pID, 
    MAX(CASE WHEN table1.key_name = 'series' THEN table1.value END) AS `series`,
    MAX(CASE WHEN table1.key_name = 'wood' THEN table1.value END) AS `wood`,
    MAX(CASE WHEN table1.key_name = 'language' THEN table1.value END) AS `language`
FROM table1
INNER JOIN table2 ON table2.id = table1.pID
WHERE table2.type = 'product'
GROUP BY table1.pID;

注意：你原SQL里的table2.id.type属于语法错误，已修正为合理的table2.type，请根据你实际的表结构调整WHERE条件的字段名。

二、65个key无需手动写SQL，用动态SQL自动生成执行 #

如果key_name的取值有65个，手动编写所有CASE语句效率太低，可以用MariaDB的预处理动态SQL，自动拼接所有字段的逻辑，一次性执行：

场景1：插入数据到新结果表 #

SET @sql = NULL;
-- 自动拼接所有key对应的CASE逻辑
SELECT GROUP_CONCAT(
    DISTINCT CONCAT(
        'MAX(CASE WHEN key_name = ''', key_name, ''' THEN value END) AS `', key_name, '`'
    )
    ORDER BY key_name
) INTO @sql
FROM table1;

-- 拼接完整的INSERT语句
SET @full_sql = CONCAT(
    'INSERT INTO results (`pID`, ', 
    (SELECT GROUP_CONCAT(DISTINCT CONCAT('`', key_name, '`')) FROM table1),
    ') SELECT table1.pID, ',
    @sql,
    ' FROM table1 INNER JOIN table2 ON table2.id = table1.pID WHERE table2.type = ''product'' GROUP BY table1.pID;'
);

-- 执行动态生成的SQL
PREPARE stmt FROM @full_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

场景2：更新已存在的目标表 #

如果你的需求是更新已经建好的目标表，不需要写65条UPDATE语句，用以下动态SQL即可批量实现：

SET @update_sql = NULL;
SELECT GROUP_CONCAT(
    DISTINCT CONCAT(
        'dest_tbl.`', key_name, '` = CASE WHEN start_tbl.key_name = ''', key_name, ''' THEN start_tbl.value ELSE dest_tbl.`', key_name, '` END'
    )
) INTO @update_sql
FROM start_tbl;

SET @full_update = CONCAT(
    'UPDATE dest_tbl INNER JOIN start_tbl ON dest_tbl.pID = start_tbl.pID SET ',
    @update_sql,
    ';'
);

PREPARE update_stmt FROM @full_update;
EXECUTE update_stmt;
DEALLOCATE PREPARE update_stmt;

注意：执行前请先确认目标表results/dest_tbl已经提前创建了所有对应key_name的字段，字段类型和原表的value字段类型保持一致。

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