MySQL表数据行列转换方法讲解

在MySQL数据库应用中，表数据的行列转换是一项常见且实用的操作。它能够让数据以更符合需求的形式呈现，无论是用于数据分析、报表生成还是其他业务场景，都具有重要意义。下面将详细讲解MySQL中表数据行列转换的方法。

使用CASE语句实现简单行列转换

对于较为简单的行列转换需求，CASE语句是一个不错的选择。假设我们有一张学生成绩表student_scores，包含学生ID（student_id）、课程名称（course_name）和成绩（score）字段。现在要将每门课程的成绩显示在不同列上。 示例代码如下：

SELECT
    student_id,
    MAX(CASE WHEN course_name = '数学' THEN score END) AS 数学成绩,
    MAX(CASE WHEN course_name = '英语' THEN score END) AS 英语成绩
FROM
    student_scores
GROUP BY
    student_id;

这里通过CASE语句对每门课程进行判断，当课程名称匹配时返回对应的成绩，然后使用MAX函数确保每个学生的成绩只出现一次。

使用PIVOT函数（MySQL 8.0以上版本支持）

MySQL 8.0及以上版本引入了PIVOT函数，为行列转换提供了更简洁的语法。同样以上述学生成绩表为例，使用PIVOT函数的示例代码如下：

SELECT *
FROM
    student_scores
PIVOT(
    MAX(score)
    FOR course_name IN ('数学', '英语')
) AS pivoted_table;

PIVOT函数的第一个参数指定要聚合的函数，FOR关键字后面跟着要转换为列的字段，IN关键字列出要转换的具体值。

使用动态SQL实现动态行列转换

如果要转换的列值是动态变化的，无法提前确定，就需要使用动态SQL来实现。示例代码如下：

SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT(
            'MAX(CASE WHEN course_name = ''',
            course_name,
            ''' THEN score END) AS `',
            course_name, '`'
        )
    ) INTO @sql
FROM
    student_scores;

SET @sql = CONCAT('SELECT student_id, ', @sql,'FROM student_scores GROUP BY student_id');

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

这段代码首先通过GROUP_CONCAT函数动态生成CASE语句部分，然后将其拼接成完整的SQL语句，最后使用PREPARE和EXECUTE执行动态SQL。

掌握这些MySQL表数据行列转换方法，能有效提升数据处理和分析的效率，满足各种复杂的业务需求。