MySQL动态行转列优化需求：将多行CS值转为多列并提效

原表结构及数据 #

期望输出表 #

现有问题 #

需实现上述表结构转换，现有SQL可完成功能，但大数据集下执行耗时过长，需要高效的动态SQL方案。

现有查询代码（注：代码中cost_center疑似应为原表的cs字段） #

select distinct or_id,emp_id,
(select cs from (
select distinct cost_center from orl where emp_id=m.emp_id) a limit 1 offset 0 ) cs1,
(select cost_center from (
select distinct cost_center from orl  where emp_id=m.emp_id) a limit 1 offset 1 ) cs2,
(select cost_center from (
select distinct cost_center from orl where emp_id=m.emp_id) a limit 1 offset 2 ) cs3
from orl m

核心优化思路 #

1. 仅做一次去重+序号分配：先对(or_id, emp_id, cs)去重，再用窗口函数为每个分组内的cs生成唯一序号，避免重复扫描原表
2. 动态生成列：自动根据分组内最大的cs数量生成对应列，无需硬编码列数
3. 明确排序规则：保证cs值的顺序稳定，避免原代码无排序导致的结果随机性

方案1：MySQL 动态SQL（条件聚合实现） #

-- 1. 获取分组内最大的cs数量，确定需要生成的列数
SET @max_cs_count = (
    SELECT MAX(cs_count)
    FROM (
        SELECT COUNT(DISTINCT cs) AS cs_count
        FROM orl
        GROUP BY or_id, emp_id
    ) t
);

-- 2. 动态生成条件聚合的列语句
SET @sql_cols = NULL;
SELECT GROUP_CONCAT(
    CONCAT('MAX(CASE WHEN seq = ', i, ' THEN cs END) AS CS', i)
    SEPARATOR ', '
) INTO @sql_cols
FROM (
    -- 生成从1到max_cs_count的序号，MySQL 8.0+可改用递归CTE生成
    SELECT 1 + t.i AS i
    FROM (SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t
    WHERE t.i < @max_cs_count
) t;

-- 3. 拼接完整SQL并执行
SET @full_sql = CONCAT(
    'SELECT or_id, emp_id, ', @sql_cols, '
    FROM (
        SELECT 
            or_id, emp_id, cs,
            -- 按cs排序生成序号，保证顺序稳定
            ROW_NUMBER() OVER(PARTITION BY or_id, emp_id ORDER BY cs) AS seq
        FROM (
            -- 仅做一次去重
            SELECT DISTINCT or_id, emp_id, cs
            FROM orl
        ) t_distinct
    ) t_rn
    GROUP BY or_id, emp_id'
);

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

方案2：PostgreSQL 动态SQL（使用crosstab函数） #

-- 先安装tablefunc扩展（首次执行）
CREATE EXTENSION IF NOT EXISTS tablefunc;

-- 动态生成PIVOT语句
WITH cs_seq AS (
    SELECT DISTINCT or_id, emp_id, cs,
           ROW_NUMBER() OVER(PARTITION BY or_id, emp_id ORDER BY cs) AS seq
    FROM orl
), max_seq AS (
    SELECT MAX(seq) AS max_num FROM cs_seq
)
SELECT format(
    'SELECT * FROM crosstab(
        ''SELECT or_id, emp_id, seq, cs FROM cs_seq ORDER BY or_id, emp_id, seq'',
        ''SELECT generate_series(1, %s)''
    ) AS ct(or_id INT, emp_id INT, %s);',
    max_num,
    array_to_string(array_agg('CS' || generate_series(1, max_num)), ' TEXT, ') || ' TEXT'
) INTO @sql
FROM max_seq;

-- 执行动态SQL
EXECUTE @sql;

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