如何关联多表查询插入数据生成指定结构的订单元数据结果表

核心逻辑 #

• 先将表2的tracking_code、tracking_url两个列做列转行处理，拆分为两行数据，分别对应meta_key的两个枚举值
• 通过order_id字段关联表3，匹配获取对应order_item_id
• 结果表的meta_id如果设置为自增主键，插入时可自动生成，也可通过窗口函数手动生成

全数据库兼容实现（UNION ALL 方案） #

该方案支持所有主流关系型数据库，无需依赖数据库高级语法特性：

直接查询结果的SQL语句 #

SELECT
    -- 如需手动生成连续meta_id可取消下方注释
    -- ROW_NUMBER() OVER(ORDER BY t3.order_item_id, tmp.meta_key) AS meta_id,
    t3.order_item_id,
    tmp.meta_key,
    tmp.meta_value
FROM (
    SELECT order_id, 'tracking_code' AS meta_key, tracking_code AS meta_value FROM 表2
    UNION ALL
    SELECT order_id, 'tracking_url' AS meta_key, tracking_url AS meta_value FROM 表2
) tmp
JOIN 表3 t3 ON tmp.order_id = t3.order_id;

插入数据到结果表的SQL语句 #

若表1的meta_id为自增主键，直接执行以下语句即可完成数据写入：

INSERT INTO 表1 (order_item_id, meta_key, meta_value)
SELECT
    t3.order_item_id,
    tmp.meta_key,
    tmp.meta_value
FROM (
    SELECT order_id, 'tracking_code' AS meta_key, tracking_code AS meta_value FROM 表2
    UNION ALL
    SELECT order_id, 'tracking_url' AS meta_key, tracking_url AS meta_value FROM 表2
) tmp
JOIN 表3 t3 ON tmp.order_id = t3.order_id;

高阶语法简化实现（支持PostgreSQL/MySQL 8.0+/SQL Server） #

使用侧写/行值构造语法，无需重复编写关联逻辑，执行效率更高：

SELECT
    t3.order_item_id,
    s.meta_key,
    s.meta_value
FROM 表2 t2
JOIN 表3 t3 ON t2.order_id = t3.order_id
CROSS JOIN LATERAL (
    VALUES
        ('tracking_code', t2.tracking_code),
        ('tracking_url', t2.tracking_url)
) AS s(meta_key, meta_value);

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