将my_table同call_id多记录合并后插入table_verif表的技术问询

核心思路是按call_id、datetime、process_name分组，给每组内的记录编号，再通过条件聚合或专用函数将多行的param_1映射到param_1至param_9的列中，以下是主流数据库的具体实现：

MySQL 实现 #

版本8.0+（支持窗口函数） #

先通过ROW_NUMBER()给每组记录生成序号，再用条件聚合将序号对应的param_1转成目标列：

INSERT INTO table_verif (call_id, datetime, process_name, param_1, param_2, param_3, param_4, param_5, param_6, param_7, param_8, param_9)
SELECT 
    call_id,
    datetime,
    process_name,
    MAX(CASE WHEN rn = 1 THEN param_1 END) AS param_1,
    MAX(CASE WHEN rn = 2 THEN param_1 END) AS param_2,
    MAX(CASE WHEN rn = 3 THEN param_1 END) AS param_3,
    MAX(CASE WHEN rn = 4 THEN param_1 END) AS param_4,
    MAX(CASE WHEN rn = 5 THEN param_1 END) AS param_5,
    MAX(CASE WHEN rn = 6 THEN param_1 END) AS param_6,
    MAX(CASE WHEN rn = 7 THEN param_1 END) AS param_7,
    MAX(CASE WHEN rn = 8 THEN param_1 END) AS param_8,
    MAX(CASE WHEN rn = 9 THEN param_1 END) AS param_9
FROM (
    SELECT 
        call_id,
        datetime,
        process_name,
        param_1,
        -- 按分组生成行号，排序规则可根据实际需求调整
        ROW_NUMBER() OVER (PARTITION BY call_id, datetime, process_name ORDER BY param_1) AS rn
    FROM my_table
) AS ranked_records
GROUP BY call_id, datetime, process_name;

版本8.0以下（无窗口函数） #

用用户变量生成组内行号，再执行条件聚合：

INSERT INTO table_verif (call_id, datetime, process_name, param_1, param_2, param_3, param_4, param_5, param_6, param_7, param_8, param_9)
SELECT 
    call_id,
    datetime,
    process_name,
    MAX(CASE WHEN rn = 1 THEN param_1 END) AS param_1,
    MAX(CASE WHEN rn = 2 THEN param_1 END) AS param_2,
    MAX(CASE WHEN rn = 3 THEN param_1 END) AS param_3,
    MAX(CASE WHEN rn = 4 THEN param_1 END) AS param_4,
    MAX(CASE WHEN rn = 5 THEN param_1 END) AS param_5,
    MAX(CASE WHEN rn = 6 THEN param_1 END) AS param_6,
    MAX(CASE WHEN rn = 7 THEN param_1 END) AS param_7,
    MAX(CASE WHEN rn = 8 THEN param_1 END) AS param_8,
    MAX(CASE WHEN rn = 9 THEN param_1 END) AS param_9
FROM (
    SELECT 
        call_id,
        datetime,
        process_name,
        param_1,
        -- 用变量跟踪分组，生成行号
        @row_num := IF(@prev_call = call_id AND @prev_dt = datetime AND @prev_proc = process_name, @row_num + 1, 1) AS rn,
        @prev_call := call_id,
        @prev_dt := datetime,
        @prev_proc := process_name
    FROM my_table, 
         (SELECT @row_num := 0, @prev_call := '', @prev_dt := '', @prev_proc := '') AS init_vars
    ORDER BY call_id, datetime, process_name, param_1
) AS ranked_records
GROUP BY call_id, datetime, process_name;

PostgreSQL 实现 #

PostgreSQL提供了crosstab函数专门处理行列转换，需先启用tablefunc扩展：

-- 首次使用需安装扩展
CREATE EXTENSION IF NOT EXISTS tablefunc;

INSERT INTO table_verif (call_id, datetime, process_name, param_1, param_2, param_3, param_4, param_5, param_6, param_7, param_8, param_9)
SELECT 
    call_id, datetime, process_name,
    param_1, param_2, param_3, param_4, param_5, param_6, param_7, param_8, param_9
FROM crosstab(
    -- 子查询生成带行号的源数据
    'SELECT call_id, datetime, process_name, rn, param_1
     FROM (
         SELECT 
             call_id,
             datetime,
             process_name,
             param_1,
             ROW_NUMBER() OVER (PARTITION BY call_id, datetime, process_name ORDER BY param_1) AS rn
         FROM my_table
     ) AS ranked
     ORDER BY 1,2,3',
    -- 指定目标列的序号范围（1到9）
    'SELECT generate_series(1,9)'
) AS ct(
    call_id INT, 
    datetime DATE, 
    process_name TEXT, 
    param_1 TEXT, param_2 TEXT, param_3 TEXT, 
    param_4 TEXT, param_5 TEXT, param_6 TEXT, 
    param_7 TEXT, param_8 TEXT, param_9 TEXT
);

注意事项 #

• 排序规则：示例中按param_1排序生成行号，若需按记录的实际发生顺序（如插入时间），需替换ORDER BY后的字段（比如表中有create_time则用ORDER BY create_time）。
• 空值处理：如果某组记录不足9条，对应的param_N会为NULL，可通过COALESCE(MAX(...), '')将空值替换为空字符串或其他默认值。

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