mysql sql列转行函数

基础概念

MySQL中的列转行操作通常是指将一个表中的多列数据转换为多行数据，这种操作在数据处理和分析中非常常见。MySQL本身并没有提供直接的列转行函数，但可以通过SQL查询和连接操作来实现。

类型

UNION ALL：用于合并多个SELECT语句的结果集。
JOIN：通过连接操作将多列数据转换为多行数据。
CASE WHEN：通过条件语句实现列转行。

应用场景

数据报表：在生成数据报表时，经常需要将多列数据转换为多行数据以便于展示。
数据分析：在进行数据分析时，列转行可以帮助我们更好地理解和分析数据。
数据转换：在数据迁移或数据清洗过程中，列转行是常见的操作。

示例代码

假设我们有一个表student_scores，结构如下：

CREATE TABLE student_scores (
    student_id INT,
    subject VARCHAR(50),
    score INT
);

我们希望将每个学生的各科成绩转换为多行数据，可以使用以下SQL查询：

SELECT student_id, 'Math' AS subject, math_score AS score
FROM (
    SELECT student_id, 
           MAX(CASE WHEN subject = 'Math' THEN score END) AS math_score,
           MAX(CASE WHEN subject = 'Science' THEN score END) AS science_score,
           MAX(CASE WHEN subject = 'English' THEN score END) AS english_score
    FROM student_scores
    GROUP BY student_id
) AS subquery
UNION ALL
SELECT student_id, 'Science' AS subject, science_score AS score
FROM (
    SELECT student_id, 
           MAX(CASE WHEN subject = 'Math' THEN score END) AS math_score,
           MAX(CASE WHEN subject = 'Science' THEN score END) AS science_score,
           MAX(CASE WHEN subject = 'English' THEN score END) AS english_score
    FROM student_scores
    GROUP BY student_id
) AS subquery
UNION ALL
SELECT student_id, 'English' AS subject, english_score AS score
FROM (
    SELECT student_id, 
           MAX(CASE WHEN subject = 'Math' THEN score END) AS math_score,
           MAX(CASE WHEN subject = 'Science' THEN score END) AS science_score,
           MAX(CASE WHEN subject = 'English' THEN score END) AS english_score
    FROM student_scores
    GROUP BY student_id
) AS subquery;