基于id与sqn实现MySQL行转列（MariaDB 10.2环境）

老哥，我刚好处理过类似的MariaDB动态转宽表需求，结合你用的10.2版本和HeidiSQL，给你一套完全自动化的方案，再也不用手动折腾几百上千张表了！

核心思路 #

因为每个id的sqn是连续从1开始的，但无法预知最大sqn数，而且要处理大量频繁更新的表，静态PIVOT肯定行不通，必须用动态SQL自动生成对应列。MariaDB 10.2支持递归CTE（Common Table Expression），刚好可以用来生成sqn序列，然后拼接出转宽需要的CASE语句。

步骤1：单表转宽的动态SQL示例 #

假设你的原始表叫source_table，结构如下：

CREATE TABLE source_table (
    id INT,
    sqn INT,
    tpn VARCHAR(50), -- 每个id唯一
    sqft DECIMAL(10,2),
    amnt DECIMAL(10,2),
    `date` DATE
);

执行以下动态SQL就能直接得到宽表结果：

-- 1. 获取当前表的最大sqn
SELECT MAX(sqn) INTO @max_sqn FROM source_table;

-- 2. 生成转宽需要的列片段（用递归CTE生成sqn序列，拼接CASE语句）
WITH RECURSIVE sqn_seq AS (
    SELECT 1 AS sqn_num
    UNION ALL
    SELECT sqn_num + 1 FROM sqn_seq WHERE sqn_num < @max_sqn
)
SELECT GROUP_CONCAT(
    CONCAT(
        'MAX(CASE WHEN sqn = ', sqn_num, ' THEN sqft END) AS sqft_', sqn_num, ',',
        'MAX(CASE WHEN sqn = ', sqn_num, ' THEN amnt END) AS amnt_', sqn_num, ',',
        'MAX(CASE WHEN sqn = ', sqn_num, ' THEN `date` END) AS date_', sqn_num
    )
    SEPARATOR ','
) INTO @pivot_columns FROM sqn_seq;

-- 3. 拼接完整查询语句并执行
SET @sql = CONCAT(
    'SELECT id, MAX(tpn) AS tpn, ', @pivot_columns, ' ',
    'FROM source_table ',
    'GROUP BY id ',
    'ORDER BY id;'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

在HeidiSQL里直接运行这段代码，就能看到每个id对应唯一一行的宽表结果了。

步骤2：封装成存储过程，批量处理所有表 #

手动写SQL处理几百张表太疯了，把上面的逻辑封装成存储过程，传入表名就能自动处理：

DELIMITER //
CREATE PROCEDURE PivotLongToWide(IN table_name VARCHAR(255))
BEGIN
    DECLARE max_sqn INT;
    DECLARE pivot_columns TEXT;
    DECLARE sql_stmt TEXT;
    
    -- 获取当前表的最大sqn（处理带特殊字符的表名，用反引号包裹）
    SET @get_max_sqn = CONCAT('SELECT MAX(sqn) INTO @max_sqn FROM `', table_name, '`;');
    PREPARE stmt FROM @get_max_sqn;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SET max_sqn = @max_sqn;
    
    -- 空表直接返回提示
    IF max_sqn IS NULL THEN
        SELECT CONCAT('表 ', table_name, ' 无数据，跳过处理') AS 提示信息;
        RETURN;
    END IF;
    
    -- 生成转宽列片段
    WITH RECURSIVE sqn_seq AS (
        SELECT 1 AS sqn_num
        UNION ALL
        SELECT sqn_num + 1 FROM sqn_seq WHERE sqn_num < max_sqn
    )
    SELECT GROUP_CONCAT(
        CONCAT(
            'MAX(CASE WHEN sqn = ', sqn_num, ' THEN sqft END) AS sqft_', sqn_num, ',',
            'MAX(CASE WHEN sqn = ', sqn_num, ' THEN amnt END) AS amnt_', sqn_num, ',',
            'MAX(CASE WHEN sqn = ', sqn_num, ' THEN `date` END) AS date_', sqn_num
        )
        SEPARATOR ','
    ) INTO pivot_columns FROM sqn_seq;
    
    -- 拼接完整查询
    SET sql_stmt = CONCAT(
        'SELECT id, MAX(tpn) AS tpn, ', pivot_columns, ' ',
        'FROM `', table_name, '` ',
        'GROUP BY id ',
        'ORDER BY id;'
    );
    
    -- 执行动态SQL
    PREPARE stmt FROM sql_stmt;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

使用的时候，只需要在HeidiSQL里调用：

CALL PivotLongToWide('你的表名');

步骤3：全自动批量处理所有符合结构的表 #

如果你的几百张表都是相同结构（都有id/sqn/tpn/sqft/amnt/date列），可以再写一个存储过程自动遍历所有符合条件的表，逐个处理：

DELIMITER //
CREATE PROCEDURE PivotAllMatchingTables()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE tbl_name VARCHAR(255);
    -- 游标：自动筛选出符合结构的表
    DECLARE tbl_cursor CURSOR FOR
        SELECT table_name
        FROM information_schema.columns
        WHERE table_schema = DATABASE() -- 只处理当前数据库的表
          AND column_name IN ('id', 'sqn', 'tpn', 'sqft', 'amnt', 'date')
        GROUP BY table_name
        HAVING COUNT(DISTINCT column_name) = 6; -- 确保6列都存在
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    OPEN tbl_cursor;
    
    read_loop: LOOP
        FETCH tbl_cursor INTO tbl_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 打印当前处理的表名（HeidiSQL里能看到输出）
        SELECT CONCAT('正在处理表：', tbl_name) AS 处理进度;
        -- 调用转宽存储过程
        CALL PivotLongToWide(tbl_name);
    END LOOP;
    
    CLOSE tbl_cursor;
    SELECT '所有符合条件的表处理完成！' AS 最终提示;
END //
DELIMITER ;

调用这个存储过程，就能全自动处理所有表了：

CALL PivotAllMatchingTables();

一些实用提示 #

• 性能优化：给每个表的id和sqn加联合索引，能大幅提升GROUP BY的速度：

  CREATE INDEX idx_id_sqn ON `你的表名`(id, sqn);
  

• 保存结果到新表：如果需要把宽表保存下来，修改存储过程里的SELECT语句为CREATE TABLE 新表名 AS SELECT ...即可，比如：

  SET sql_stmt = CONCAT(
      'CREATE TABLE `', table_name, '_wide` AS ',
      'SELECT id, MAX(tpn) AS tpn, ', pivot_columns, ' ',
      'FROM `', table_name, '` ',
      'GROUP BY id ',
      'ORDER BY id;'
  );
  

• HeidiSQL使用技巧：执行存储过程后，直接在结果窗口就能看到宽表数据，也可以右键导出为CSV/Excel等格式。

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