在处理数据时，我们常常会遇到需要将表中的列（字段）转换为行，或将行转换为列的情况。这种操作通常被称为“列转行”（Pivoting）和“行转列”（Unpivoting）。在 MySQL 中，虽然没有直接提供 PIVOT 和 UNPIVOT 这样的关键字，但我们可以使用其他方法来实现这些功能。本文将向您介绍如何使用 CASE 语句、聚合函数以及 GROUP BY 子句来完成列转行和行转列的操作。
列转行（Pivoting）
列转行是指将表格中的一列或多列的值转换成新的列标题，并且将对应的数据填充到这些新列中。下面通过一个例子来说明这个过程。
示例数据
假设有一个成绩表 scores，包含学生的姓名 name、科目 subject 和分数 score：
1 2 3 4 5 6 7 8 9 10 11 | CREATE TABLE scores (
name VARCHAR(50),
subject VARCHAR(20),
score INT
);
INSERT INTO scores (name, subject, score) VALUES
('Alice', 'Math', 95),
('Alice', 'English', 88),
('Bob', 'Math', 76),
('Bob', 'English', 92);
|
转换前查询结果
1 2 3 4 5 6 7 8 9 | SELECT * FROM scores;
+
| name | subject | score |
+
| Alice | Math | 95 |
| Alice | English | 88 |
| Bob | Math | 76 |
| Bob | English | 92 |
+
|
列转行 SQL 语句
我们需要将 subject 列的不同值变为新的列名，并把对应的 score 填充进去。
1 2 3 4 5 6 7 8 | SELECT
name,
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
scores
GROUP BY
name;
|
转换后查询结果
1 2 3 4 5 6 | +
| name | Math | English |
+
| Alice | 95 | 88 |
| Bob | 76 | 92 |
+
|
行转列（Unpivoting）
行转列是列转行的逆过程，即将多个列的数据转换成一行多条记录的形式。这可以通过 UNION ALL 来实现。
示例数据
假设现在有另一个表 students，它已经以列转行后的形式存储了学生的信息：
1 2 3 4 5 6 7 8 9 | CREATE TABLE students (
name VARCHAR(50),
Math INT,
English INT
);
INSERT INTO students (name, Math, English) VALUES
('Alice', 95, 88),
('Bob', 76, 92);
|
转换前查询结果
1 2 3 4 5 6 7 | SELECT * FROM students;
+
| name | Math | English |
+
| Alice | 95 | 88 |
| Bob | 76 | 92 |
+
|
行转列 SQL 语句
我们将每个科目的成绩都变成单独的一行记录。
1 2 3 4 5 6 7 8 9 10 11 12 13 | SELECT
name,
'Math' AS subject,
Math AS score
FROM
students
UNION ALL
SELECT
name,
'English' AS subject,
English AS score
FROM
students;
|
转换后查询结果
1 2 3 4 5 6 7 8 | +
| name | subject | score |
+
| Alice | Math | 95 |
| Bob | Math | 76 |
| Alice | English | 88 |
| Bob | English | 92 |
+
|
通过以上示例，我们可以看到如何在 MySQL 中灵活地进行列转行和行转列的数据转换。希望这些技巧能够帮助您更好地管理和分析数据库中的数据。
