基于参考表创建列名不同的新表：结构差异表关联实现求助

我有两张表：一张是Raw表（原始表），另一张是用于字段映射的Specnorm表。由于两张表结构不同，不知道如何关联，希望基于Specnorm表的映射规则创建两张新表。以下是示例数据和预期输出：

Raw表 #

Specnorm表（字段映射规则） #

预期输出 #

Screener表 #

Harvest表（对应Specnorm中的Product映射） #

方式一：使用SQL实现 #

静态生成目标表（字段固定场景） #

-- 创建Screener表
CREATE TABLE Screener AS
SELECT 
    Sbjnum,
    B_01 AS Area1,
    B_02 AS Area2
FROM Raw;

-- 创建Harvest表
CREATE TABLE Harvest AS
SELECT 
    Sbjnum,
    C_01 AS ProductID1,
    C_02 AS ProductID2
FROM Raw;

动态生成目标表（映射规则可能变化场景，以MySQL为例） #

通过Specnorm表自动拼接SQL语句，无需硬编码字段：

-- 生成Screener表的动态SQL
SET @sql_screener = (
    SELECT CONCAT(
        'CREATE TABLE Screener AS SELECT Sbjnum, ',
        GROUP_CONCAT(CONCAT(fieldname, ' AS ', destfieldname) ORDER BY sequence SEPARATOR ', '),
        ' FROM Raw'
    )
    FROM Specnorm
    WHERE tablename = 'Screener'
);

PREPARE stmt FROM @sql_screener;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 生成Harvest表的动态SQL
SET @sql_harvest = (
    SELECT CONCAT(
        'CREATE TABLE Harvest AS SELECT Sbjnum, ',
        GROUP_CONCAT(CONCAT(fieldname, ' AS ', destfieldname) ORDER BY sequence SEPARATOR ', '),
        ' FROM Raw'
    )
    FROM Specnorm
    WHERE tablename = 'Product'
);

PREPARE stmt FROM @sql_harvest;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

方式二：使用Python Pandas实现 #

适合数据处理场景，灵活适配映射规则变化：

import pandas as pd

# 读取Raw表数据
raw_df = pd.DataFrame({
    'Sbjnum': ['172009172', '172009173', '172009174'],
    'B_01': [100, 200, 300],
    'B_02': [120, 140, 150],
    'C_01': [200, 220, 240],
    'C_02': [240, 250, 260]
})

# 读取Specnorm表数据
specnorm_df = pd.DataFrame({
    'tablename': ['Screener', 'Screener', 'Product', 'Product'],
    'fieldname': ['B_01', 'B_02', 'C_01', 'C_02'],
    'destfieldname': ['Area1', 'Area2', 'ProductID1', 'ProductID2'],
    'sequence': [1, 2, 3, 4]
})

# 生成Screener表
screener_mapping = specnorm_df[specnorm_df['tablename'] == 'Screener'].set_index('fieldname')['destfieldname']
screener_df = raw_df[['Sbjnum'] + screener_mapping.index.tolist()].rename(columns=screener_mapping)

# 生成Harvest表
harvest_mapping = specnorm_df[specnorm_df['tablename'] == 'Product'].set_index('fieldname')['destfieldname']
harvest_df = raw_df[['Sbjnum'] + harvest_mapping.index.tolist()].rename(columns=harvest_mapping)

# 输出结果
print("Screener表：")
print(screener_df)
print("\nHarvest表：")
print(harvest_df)

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