MySQL多列数据转成多行数据（列转行）

在MySQL中，将多列数据转换成多行数据通常涉及到使用UNION ALL或者CASE语句来动态构建查询。以下是一个基本的示例，说明如何实现这一转换：

假设你有一个名为students的表，其中包含student_id（学生ID）、math_score（数学成绩）、english_score（英语成绩）和science_score（科学成绩）等字段。你想要将每个学生的所有成绩转换成多行，每一行包含一个科目的成绩。

SELECT student_id, 'Math' AS subject, math_score
FROM students
UNION ALL
SELECT student_id, 'English', english_score
FROM students
UNION ALL
SELECT student_id, 'Science', science_score
FROM students;

在这个查询中：

• UNION ALL用于合并多个SELECT语句的结果集。由于UNION ALL会包含重复的行，所以如果你的数据中有重复的学生ID，这个查询会返回重复的行。
• 每个SELECT语句都从students表中选择不同的成绩列，并为每一行添加一个科目名称。

如果你想要避免重复的学生ID，可以使用UNION代替UNION ALL，但这通常需要更复杂的查询，因为UNION会自动去重。

在某些情况下，如果列的数量是动态的，你可能需要使用动态SQL来构建查询。这通常涉及到使用CASE语句或者IF语句来处理不同的列，或者使用存储过程和变量来构建查询字符串。

例如，如果你有一个动态的列列表，你可以使用以下方法：

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SELECT student_id, ',
      QUOTEIDENTIFIER(column_name),
      ' AS subject, ',
      QUOTEIDENTIFIER(column_name),
      ' FROM students'
    )
  ) INTO @sql
FROM information_schema.columns
WHERE table_name = 'students' AND column_name LIKE '%_score';

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

在这个例子中，我们首先构建了一个动态的SQL查询字符串，然后准备、执行并释放了这个查询。这种方法可以处理动态数量的列，但需要更复杂的SQL和可能的存储过程编写。