在成绩管理系统中，学生的科目成绩通常按纵向方式存储在数据库中，一条记录对应一个学生的某一科成绩，但是在展示时往往需要横向展示，即一个学生一行，不同科目作为不同的列，这就需要用到列转行也就是Pivot的操作。MySQL没有原生的Pivot函数，我们可以通过CASE WHEN语句或者动态SQL来实现这个需求。
基础表结构设计
首先我们创建存储学生成绩的基础表，表结构如下：
-- 创建学生成绩表
CREATE TABLE student_score (
id INT PRIMARY KEY AUTO_INCREMENT,
student_name VARCHAR(50) NOT NULL COMMENT '学生姓名',
subject VARCHAR(50) NOT NULL COMMENT '科目名称',
score DECIMAL(5,2) NOT NULL COMMENT '成绩'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生成绩表';
-- 插入测试数据
INSERT INTO student_score (student_name, subject, score) VALUES
('张三', '语文', 92.5),
('张三', '数学', 88.0),
('张三', '英语', 95.0),
('李四', '语文', 85.0),
('李四', '数学', 90.5),
('李四', '英语', 87.0),
('王五', '语文', 78.0),
('王五', '数学', 92.0),
('王五', '英语', 89.5);
静态列转行实现
如果已知所有科目是固定的，比如只有语文、数学、英语三科，可以使用CASE WHEN语句实现静态的列转行，核心逻辑是对每个科目做条件判断，将对应成绩取出作为新列的值。
SELECT
student_name,
MAX(CASE WHEN subject = '语文' THEN score END) AS 语文,
MAX(CASE WHEN subject = '数学' THEN score END) AS 数学,
MAX(CASE WHEN subject = '英语' THEN score END) AS 英语
FROM student_score
GROUP BY student_name;
这里使用MAX函数是因为CASE WHEN会对每个学生的每条成绩记录做判断，同一个学生的同一科目只会有一个有效值，其他科目对应的值为NULL，聚合函数会忽略NULL值，所以可以用MAX或者SUM来取到对应科目的成绩。执行上述语句后，会得到如下结果：
| student_name | 语文 | 数学 | 英语 |
|---|---|---|---|
| 张三 | 92.5 | 88.0 | 95.0 |
| 李四 | 85.0 | 90.5 | 87.0 |
| 王五 | 78.0 | 92.0 | 89.5 |
动态列转行实现
实际业务中科目可能会动态增加，比如新增物理、化学等科目，静态SQL就需要手动修改，不够灵活。这时候可以通过动态SQL自动获取所有科目，拼接出完整的列转行语句。
实现步骤
- 第一步：查询所有不重复的科目名称，拼接成CASE WHEN的片段
- 第二步：拼接完整的查询SQL语句
- 第三步：执行拼接好的动态SQL
完整的动态SQL实现代码如下：
-- 设置变量存储拼接的SQL
SET @sql = NULL;
-- 拼接CASE WHEN片段
SELECT GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(CASE WHEN subject = ''',
subject,
''' THEN score END) AS `',
subject,
'`'
)
) INTO @sql
FROM student_score;
-- 拼接完整的查询语句
SET @sql = CONCAT('SELECT student_name, ', @sql,
' FROM student_score GROUP BY student_name');
-- 预处理并执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
上述代码中，GROUP_CONCAT函数会把所有科目的CASE WHEN片段拼接成一个字符串，然后和固定的查询前缀、分组语句拼接成完整的SQL。如果后续新增了科目，只要往student_score表中插入对应数据，再次执行这段动态SQL就可以自动展示新的科目列，不需要手动修改代码。
注意事项
- 如果科目名称包含特殊字符或者空格，拼接的时候需要用反引号把列名包裹起来，避免SQL语法错误
GROUP_CONCAT函数默认的长度是1024字符，如果科目数量很多，超过了这个长度，需要提前设置group_concat_max_len参数，比如SET SESSION group_concat_max_len = 100000;- 动态SQL执行的时候要注意权限问题，执行用户需要有对应表的查询权限，以及执行预处理语句的权限
列转行的核心逻辑是通过条件判断将纵向的不同值映射到横向的不同列，静态场景用固定CASE WHEN即可，动态场景结合动态SQL可以大幅提升代码的灵活性，适配业务变化。