MySQL行变列|极客笔记

MySQL行变列

在MySQL数据库中，有时候需要将表中的行数据转换为列数据，这种操作称为行变列。行变列可以在一些特定的业务场景下扮演重要的角色，例如在报表生成、数据显示等方面。

本文将通过介绍MySQL中实现行变列的多种方法来帮助读者更好地理解和应用这一技术。接下来将从以下几个方面进行详细展开：

使用CASE WHEN语句进行行变列转换
使用GROUP_CONCAT函数进行行变列转换
使用动态SQL进行行变列转换

使用CASE WHEN语句进行行变列转换

CASE WHEN语句是MySQL中的条件判断语句，可以根据条件的不同实现对数据的不同处理。通过在SELECT语句中嵌套CASE WHEN语句，可以实现将行数据转换为列数据的操作。

假设有以下一张存储学生成绩的表student_scores：

student_id	subject	score
1	Math	80
1	English	90
2	Math	85
2	English	88

现在希望将学生的成绩数据按照学生ID进行行变列转换，即将表结构转换为如下形式：

student_id	Math	English
1	80	90
2	85	88

可以通过以下SQL语句实现：

SELECT 
    student_id,
    MAX(CASE WHEN subject = 'Math' THEN score ELSE NULL END) AS Math,
    MAX(CASE WHEN subject = 'English' THEN score ELSE NULL END) AS English
FROM student_scores
GROUP BY student_id;

以上SQL语句中，使用了CASE WHEN语句将不同科目的成绩转换为不同的列，然后通过GROUP BY语句按照学生ID进行分组，最终将不同科目的成绩聚合到同一行中。

使用GROUP_CONCAT函数进行行变列转换

除了使用CASE WHEN语句，还可以使用MySQL中提供的GROUP_CONCAT函数来实现行变列的转换。GROUP_CONCAT函数的作用是将聚合函数返回的多行数据拼接成一个字符串。

假设有以下一张存储学生所选科目的表student_subjects：

student_id	subject
1	Math
1	English
2	Math
2	English

现在希望将学生所选科目的数据按照学生ID进行行变列转换，即将表结构转换为如下形式：

student_id	subjects
1	Math,English
2	Math,English

可以通过以下SQL语句实现：

SELECT 
    student_id,
    GROUP_CONCAT(subject) AS subjects
FROM student_subjects
GROUP BY student_id;

以上SQL语句中，使用了GROUP_CONCAT函数将学生所选科目进行拼接，并通过GROUP BY语句按照学生ID进行分组，最终将不同科目的数据聚合到同一行中。

使用动态SQL进行行变列转换

除了上述两种方法，还可以通过动态SQL来实现行变列的转换。动态SQL是指根据具体的情况动态生成SQL语句，实现对数据的动态操作。

假设有以下一张存储学生成绩的表student_scores：

student_id	subject	score
1	Math	80
1	English	90
2	Math	85
2	English	88

现在希望将学生的成绩数据按照学生ID进行行变列转换，即将表结构转换为如下形式：

student_id	Math	English
1	80	90
2	85	88

可以通过以下动态SQL语句实现：

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN subject = ''',
      subject,
      ''' THEN score END) AS ',
      subject
    )
  ) 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;

以上动态SQL语句中，首先通过SELECT语句动态生成将不同科目的成绩转换为不同列的部分SQL语句，然后再通过PREPARE和EXECUTE语句执行动态生成的完整SQL语句，最终实现行变列的转换操作。