MySQL 列转行unpivot

在数据库中，我们经常需要对数据进行转置操作。转置操作是将行数据转换为列数据，或者将列数据转换为行数据。在MySQL中，如果我们需要将列数据转换为行数据，我们可以使用unpivot操作。

1. 什么是列转行unpivot

列转行操作也被称为 unpivot 操作，它可以将列数据转换为行数据。一般来说，unpivot 操作适用于以下场景：

当我们需要将表的列数据转换为行数据时。
当我们需要将表中的多个列合并，生成新的列时。

2. 使用UNION ALL进行列转行unpivot

在MySQL中，我们可以使用UNION ALL操作符来执行列转行操作。下面是一个示例，假设我们有一个学生表，包含学生的ID、姓名和三门课程的成绩。

CREATE TABLE students (
    id INT,
    name VARCHAR(100),
    math_score INT,
    physics_score INT,
    chemistry_score INT
);

INSERT INTO students (id, name, math_score, physics_score, chemistry_score)
VALUES
    (1, 'John', 90, 80, 85),
    (2, 'Amy', 95, 75, 90),
    (3, 'Tom', 85, 90, 80);

我们想要将学生表的成绩列转换为行数据，生成一个新的表，包含学生ID、姓名和科目成绩。可以使用以下SQL查询：

SELECT id, name, 'Math' AS subject, math_score AS score FROM students
UNION ALL
SELECT id, name, 'Physics' AS subject, physics_score AS score FROM students
UNION ALL
SELECT id, name, 'Chemistry' AS subject, chemistry_score AS score FROM students;

这将生成一个如下所示的结果：

+------+-------+-----------+-------+
| id   | name  | subject   | score |
+------+-------+-----------+-------+
|  1   | John  | Math      |  90   |
|  2   | Amy   | Math      |  95   |
|  3   | Tom   | Math      |  85   |
|  1   | John  | Physics   |  80   |
|  2   | Amy   | Physics   |  75   |
|  3   | Tom   | Physics   |  90   |
|  1   | John  | Chemistry |  85   |
|  2   | Amy   | Chemistry |  90   |
|  3   | Tom   | Chemistry |  80   |
+------+-------+-----------+-------+

通过使用UNION ALL操作符，我们将每个科目的成绩转换为行数据，并将学生的ID和姓名与之一起展示。

3. 使用CROSS JOIN进行列转行unpivot

除了使用UNION ALL操作符，我们还可以使用CROSS JOIN进行列转行操作。这里的CROSS JOIN是将两个或多个表进行连接的操作。

以下是使用CROSS JOIN操作符进行列转行操作的示例。我们将创建一个新表来存储学生ID和科目成绩：

CREATE TABLE student_scores (
    id INT,
    subject VARCHAR(100),
    score INT
);

INSERT INTO student_scores (id, subject, score)
SELECT id, 'Math' AS subject, math_score AS score FROM students
CROSS JOIN (SELECT 1 UNION SELECT 2 UNION SELECT 3) AS t
UNION ALL
SELECT id, 'Physics' AS subject, physics_score AS score FROM students
CROSS JOIN (SELECT 1 UNION SELECT 2 UNION SELECT 3) AS t
UNION ALL
SELECT id, 'Chemistry' AS subject, chemistry_score AS score FROM students
CROSS JOIN (SELECT 1 UNION SELECT 2 UNION SELECT 3) AS t;

这将生成与之前相同的结果：

+------+-----------+-------+
| id   | subject   | score |
+------+-----------+-------+
|  1   | Math      |  90   |
|  2   | Math      |  95   |
|  3   | Math      |  85   |
|  1   | Physics   |  80   |
|  2   | Physics   |  75   |
|  3   | Physics   |  90   |
|  1   | Chemistry |  85   |
|  2   | Chemistry |  90   |
|  3   | Chemistry |  80   |
+------+-----------+-------+

在这个示例中，我们通过使用CROSS JOIN操作符将学生ID与SELECT 1 UNION SELECT 2 UNION SELECT 3的结果进行连接。这将生成三个重复的行，每个学生ID与一个科目成绩对应。