如何在MySQL中基于关联关系实现行转列合并查询结果

要将Table2中同一F_Key的多行数据合并到Table1对应的单行，本质是**行转列（Pivot）**操作，以下分两种场景给出解决方案：

一、固定最大行数场景 #

如果能确定Table2中每个F_Key最多对应N行数据，可使用窗口函数+条件聚合实现：

实现步骤 #

1. 给Table2的每行按F_Key分组并编号，确定每行的顺序；
2. 通过条件聚合，将不同编号的行数据映射为单行的多列；
3. 关联Table1，最终按P_Key分组得到结果。

示例SQL #

SELECT
    t1.P_Key,
    t1.Value,
    IFNULL(MAX(CASE WHEN rn = 1 THEN t2.`F 1` END), '--') AS `1.F1`,
    IFNULL(MAX(CASE WHEN rn = 1 THEN t2.`F 2` END), '--') AS `1.F2`,
    IFNULL(MAX(CASE WHEN rn = 2 THEN t2.`F 1` END), '--') AS `2.F1`,
    IFNULL(MAX(CASE WHEN rn = 2 THEN t2.`F 2` END), '--') AS `2.F2`,
    IFNULL(MAX(CASE WHEN rn = 3 THEN t2.`F 1` END), '--') AS `3.F1`,
    IFNULL(MAX(CASE WHEN rn = 3 THEN t2.`F 2` END), '--') AS `3.F2`
FROM Table1 t1
LEFT JOIN (
    SELECT 
        F_Key, 
        `F 1`, 
        `F 2`,
        -- 按F1排序给每组F_Key的行编号，可根据实际需求调整排序字段
        ROW_NUMBER() OVER (PARTITION BY F_Key ORDER BY `F 1`) AS rn
    FROM Table2
) t2 ON t1.P_Key = t2.F_Key
GROUP BY t1.P_Key, t1.Value;

二、动态行数场景 #

如果Table2中F_Key对应的行数不固定（可能随时新增），需要用动态SQL自动生成对应列：

实现步骤 #

1. 计算每个F_Key的最大行数，确定需要生成的列数；
2. 动态拼接条件聚合的SQL片段；
3. 执行拼接好的完整SQL语句。

示例SQL #

-- 1. 获取每个F_Key的最大行数
SET @max_rn = (SELECT MAX(rn) FROM (SELECT ROW_NUMBER() OVER (PARTITION BY F_Key ORDER BY `F 1`) AS rn FROM Table2) t);

-- 2. 动态生成列的SQL片段
SET @sql_columns = NULL;
SELECT
    GROUP_CONCAT(
        CONCAT(
            'IFNULL(MAX(CASE WHEN rn = ', rn, ' THEN t2.`F 1` END), \'--\') AS `', rn, '.F1`, ',
            'IFNULL(MAX(CASE WHEN rn = ', rn, ' THEN t2.`F 2` END), \'--\') AS `', rn, '.F2`'
        ) SEPARATOR ', '
    ) INTO @sql_columns
FROM (SELECT DISTINCT rn FROM (SELECT ROW_NUMBER() OVER (PARTITION BY F_Key ORDER BY `F 1`) AS rn FROM Table2) t) r;

-- 3. 拼接完整SQL并执行
SET @full_sql = CONCAT(
    'SELECT t1.P_Key, t1.Value, ', @sql_columns, ' ',
    'FROM Table1 t1 ',
    'LEFT JOIN (',
        'SELECT F_Key, `F 1`, `F 2`, ',
        'ROW_NUMBER() OVER (PARTITION BY F_Key ORDER BY `F 1`) AS rn ',
        'FROM Table2',
    ') t2 ON t1.P_Key = t2.F_Key ',
    'GROUP BY t1.P_Key, t1.Value;'
);

PREPARE stmt FROM @full_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

说明 #

• 上述代码中ORDER BY F 1``用于确定行的顺序，可根据业务需求替换为其他字段（如插入时间）；
• IFNULL(..., '--')用于将空值替换为--，若不需要可直接使用MAX(...)。

内容的提问来源于stack exchange，提问作者Kavinda Keshan Rasnayake