基于用户可编辑分类实现MySQL动态列查询的方案咨询

现有表结构与数据 #

grade表（成绩表） #

CREATE TABLE `grade` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `studentid` int(11) DEFAULT NULL,
  `category` varchar(100) DEFAULT NULL,
  `grade` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `grade` (`id`, `studentid`, `category`, `grade`) VALUES
(1, 1,  'PAS',  76),
(2, 1,  'PTS',  100),
(3, 2,  'PAS',  100),
(4, 3,  'PTS',  100),
(5, 4,  'PTS',  100),
(6, 4,  'PAS',  100);

tb_category表（分类表） #

CREATE TABLE `tb_category` (
  `gradecatid` int(11) NOT NULL DEFAULT 0,
  `code` varchar(3) DEFAULT NULL,
  `category_name` varchar(200) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

INSERT INTO `tb_category` (`gradecatid`, `code`, `category_name`) VALUES
(1, 'PTS',  'Mid-Semester'),
(2, 'PAS',  'End Semester');

数据展示 #

grade表数据

tb_category表数据

当前实现的局限 #

目前使用固定分类的查询语句，能实现静态列的成绩汇总：

SELECT studentid,
SUM(IFNULL(IF(category = 'PAS', grade, 0), 0)) AS grade1,
SUM(IFNULL(IF(category = 'PTS', grade, 0), 0)) AS grade2
FROM `grade`
GROUP BY studentid

但当用户在tb_category中新增或删除分类（如新增QUIZ分类）时，该语句无法自动适配，必须手动修改SQL，无法满足动态分类的需求。

需求：动态列查询 #

需要实现基于tb_category中分类的动态列查询，分类增删后自动调整查询结果的列数，预期结果形式如下（分类新增后自动扩展列）：

解决方案：使用MySQL动态SQL实现 #

MySQL本身不支持原生的动态列，但可以通过动态生成SQL语句的方式实现，推荐使用存储过程封装逻辑，自动读取分类表中的分类并生成对应的查询列：

存储过程代码 #

DELIMITER //
CREATE PROCEDURE get_dynamic_grade_report()
BEGIN
    -- 声明变量存储动态SQL和分类列表
    DECLARE dynamic_sql TEXT DEFAULT '';
    DECLARE category_code VARCHAR(100);
    DECLARE grade_index INT DEFAULT 1;
    DECLARE done INT DEFAULT 0;
    
    -- 定义游标读取所有分类code
    DECLARE category_cursor CURSOR FOR 
        SELECT code FROM tb_category ORDER BY gradecatid;
    
    -- 处理游标结束
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    -- 初始化SQL开头部分
    SET dynamic_sql = 'SELECT studentid';
    
    -- 遍历分类，拼接每个分类对应的SUM(IF...)语句
    OPEN category_cursor;
    read_loop: LOOP
        FETCH category_cursor INTO category_code;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        SET dynamic_sql = CONCAT(dynamic_sql, ', SUM(IFNULL(IF(category = ''', category_code, ''', grade, 0), 0)) AS grade', grade_index);
        SET grade_index = grade_index + 1;
    END LOOP;
    CLOSE category_cursor;
    
    -- 拼接GROUP BY部分
    SET dynamic_sql = CONCAT(dynamic_sql, ' FROM grade GROUP BY studentid');
    
    -- 执行动态SQL
    PREPARE stmt FROM dynamic_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

使用方法 #

调用存储过程即可获取动态列的成绩报告：

CALL get_dynamic_grade_report();

效果说明 #

• 当tb_category中新增分类（如新增QUIZ），存储过程会自动新增对应的gradeN列
• 当删除分类时，对应的列会从结果中移除
• 所有不存在对应分类成绩的学生，该列值会显示为0

注意事项 #

• 确保tb_category中的code值唯一，避免重复列名
• 存储过程需要有足够的权限创建和执行
• 如果分类数量过多，需注意MySQL对SQL语句长度的限制

内容的提问来源于stack exchange，提问作者bagi2info.com