MySQL动态SQL构建问题：多表关联生成动态列结果集

嘿，我完全懂你的困扰——当SecondTable里每个Ref对应的记录数不固定时，静态的COUNT(CASE WHEN...)根本没法适配，必须用动态SQL来实现你要的横向展开效果。下面我给你一套完整的解决方案，直接就能用：

核心思路 #

我们需要先统计每个Ref对应的最大记录条数，然后动态生成对应数量的StockN、CodeN、PriceN列，最后通过关联分组把纵向的记录横向拼接起来。

完整动态SQL实现（用存储过程） #

我写了一个存储过程，会自动适配SecondTable中Ref的记录数变化，你只需要调用它就能得到目标结果：

DELIMITER //

CREATE PROCEDURE GenerateDynamicResult()
BEGIN
    DECLARE max_groups INT;
    DECLARE column_sql TEXT DEFAULT '';
    DECLARE i INT DEFAULT 1;

    -- 第一步：统计每个Ref下的最大记录数，确定要生成多少组列
    SELECT MAX(rn) INTO max_groups
    FROM (
        SELECT Ref, ROW_NUMBER() OVER(PARTITION BY Ref ORDER BY ID) AS rn
        FROM SecondTable
    ) t;

    -- 第二步：动态拼接每组列的SQL片段
    WHILE i <= max_groups DO
        -- 用CHAR(64+i)生成A、B、C...作为Stock的后缀（65是ASCII的A）
        SET column_sql = CONCAT(column_sql,
            ', MAX(CASE WHEN rn = ', i, ' THEN st.ID END) AS Stock', CHAR(64 + i),
            ', MAX(CASE WHEN rn = ', i, ' THEN st.Code END) AS Code', i,
            ', MAX(CASE WHEN rn = ', i, ' THEN st.Price END) AS Price', i
        );
        SET i = i + 1;
    END WHILE;

    -- 第三步：拼接完整SQL并执行
    SET @full_sql = CONCAT(
        'SELECT ft.ID, ft.IdCust, ft.Ref', column_sql,
        ' FROM FirstTable ft',
        ' LEFT JOIN (',
            'SELECT ID, Ref, Code, Price, ROW_NUMBER() OVER(PARTITION BY Ref ORDER BY ID) AS rn',
            ' FROM SecondTable',
        ') st ON ft.Ref = st.Ref',
        ' GROUP BY ft.ID, ft.IdCust, ft.Ref',
        ' ORDER BY ft.ID, ft.Ref'
    );

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

DELIMITER ;

怎么使用？ #

直接调用这个存储过程就行：

CALL GenerateDynamicResult();

关键细节解释 #

1. 窗口函数分组编号：用ROW_NUMBER() OVER(PARTITION BY Ref ORDER BY ID)给每个Ref下的记录编上序号，这样我们就能区分同个Ref下的不同记录；
2. 动态列生成：通过循环拼接CASE WHEN语句，自动生成对应数量的StockA/Code1/Price1、StockB/Code2/Price2等列；
3. 分组聚合取值：用MAX(CASE...)来取出每个序号对应的字段值，因为同个Ref+rn组合只有一条记录，MAX能确保准确取到对应值；
4. 自动适配变化：如果后续SecondTable中某个Ref的记录数增加，再次调用存储过程会自动生成更多列，完全不用修改代码。

可选调整点 #

• 如果想要改变同个Ref下记录的排序顺序，修改窗口函数里的ORDER BY ID为你需要的字段即可；
• 如果不需要LEFT JOIN（确保每个FirstTable的Ref都在SecondTable有对应记录），可以改成INNER JOIN。

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