MySQL员工账单场景硬编码行转列改造为动态自动列的技术咨询

实现逻辑 #

通过MySQL预处理语句机制，自动读取账单类别配置表的全部分类，动态拼接聚合查询逻辑，新增账单类别后无需修改SQL即可自动生成对应结果列。

可直接运行代码 #

-- 可选：如果账单分类数量多，先调整GROUP_CONCAT长度限制，避免拼接截断
SET SESSION group_concat_max_len = 102400;

-- 1. 动态拼接所有分类对应的CASE聚合语句
SET @dynamic_case = NULL;
SELECT GROUP_CONCAT(DISTINCT
           CONCAT('SUM(CASE WHEN uc.c_name = ''', c_name, ''' THEN uc.c_amount ELSE NULL END) AS `', REPLACE(c_name, ' ', '_'), '`')
       ) INTO @dynamic_case
FROM app_fd_pr_uagecateg_bil;

-- 2. 组装完整查询SQL
SET @final_sql = CONCAT('
SELECT CONCAT(e.c_firstName, '' '', e.c_Lastname) AS NAME,
', @dynamic_case, '
FROM app_fd_pr_uage_bill ub 
INNER JOIN app_fd_pr_uagecateg_bil uc ON ub.id = uc.c_FrKeyUbill
INNER JOIN app_fd_hrm_employee e ON ub.c_employeeName = e.id
GROUP BY NAME
');

-- 3. 预处理并执行查询
PREPARE run_stmt FROM @final_sql;
EXECUTE run_stmt;
DEALLOCATE PREPARE run_stmt;

注意说明 #

• 代码可直接在HeidiSQL中运行，输出逻辑和你原有的硬编码查询完全一致
• 列名生成时自动将分类名称中的空格替换为下划线，符合MySQL列名规范，若需要自定义列名规则可修改REPLACE(c_name, ' ', '_')部分的逻辑
• 若账单分类数量超过100个，可按需调大group_concat_max_len的参数值

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