分享实现mysql行转列与列转行的示例代码

在数据库操作中，行转列与列转行是常见的需求。MySQL提供了多种方法来实现这些转换，本文将分享相关的示例代码，帮助大家更好地理解和应用。

行转列

使用CASE语句

假设有一张学生成绩表student_scores，包含字段student_id（学生ID）、subject（科目）、score（成绩）。现在要将每个学生的不同科目成绩转换为列展示。

SELECT
    student_id,
    MAX(CASE WHEN subject = '数学' THEN score END) AS math_score,
    MAX(CASE WHEN subject = '语文' THEN score END) AS chinese_score,
    MAX(CASE WHEN subject = '英语' THEN score END) AS english_score
FROM
    student_scores
GROUP BY
    student_id;

在这段代码中，通过CASE WHEN语句进行条件判断，MAX函数确保在分组时能够正确获取每个学生对应科目的成绩。

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

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

PIVOT函数使行转列操作更加简洁直观，FOR关键字指定要转换的列，IN后面列出具体的列值。

列转行

使用UNION ALL

假设有一张包含不同学科成绩列的学生成绩表student_subject_scores，字段有student_id（学生ID）、math_score（数学成绩）、chinese_score（语文成绩）、english_score（英语成绩）。要将这些列转换为行。

SELECT
    student_id,
  '数学' AS subject,
    math_score AS score
FROM
    student_subject_scores
UNION ALL
SELECT
    student_id,
    '语文' AS subject,
    chinese_score AS score
FROM
    student_subject_scores
UNION ALL
SELECT
    student_id,
    '英语' AS subject,
    english_score AS score
FROM
    student_subject_scores;

通过UNION ALL将多个查询结果合并在一起，每个查询分别将不同列的数据转换为行。

使用VALUES子句（MySQL 8.0及以上版本支持）

SELECT
    student_id,
    subject,
    score
FROM
    student_subject_scores
JOIN (
    VALUES ('数学','math_score'), ('语文', 'chinese_score'), ('英语', 'english_score')
) AS sub (subject, score_column)
ON 1 = 1
WHERE score_column IS NOT NULL;

这种方法利用VALUES子句创建一个临时表，再通过连接操作实现列转行。

掌握这些MySQL行转列与列转行的示例代码，能够在处理数据时更加灵活高效，满足不同的业务需求。无论是数据分析还是报表生成，都能轻松应对。