MySQL 数据表行列转换

1. 简介

在 MySQL 数据库中，数据以表的形式进行存储和管理。表具有行和列的结构，每行表示一个数据记录，每列表示一个数据字段。有时候，在处理数据时，需要对表的行列进行转换，以满足特定的需求。本文将详细介绍 MySQL 数据表行列转换的相关技术和方法。

2. 行转列（UNPIVOT）

2.1 原理

行转列，也称为 UNPIVOT，是将表的行数据转换为列数据的过程。具体而言，对于每一行数据，将其列字段的值作为新的一列，并将原表的其他字段与之对应。一般来说，行转列是通过 SQL 查询语句实现的。

2.2 示例

假设我们有一个存储学生信息的表 students，包含以下字段：

现在我们想要将每个学生的数学成绩和英语成绩作为新的列，得到以下表格：

实现这个转换的 SQL 查询语句如下：

SELECT 
    id, 
    name,
    MAX(CASE WHEN subject = 'Math' THEN score END) AS Math,
    MAX(CASE WHEN subject = 'English' THEN score END) AS English
FROM students
GROUP BY id, name;

上述查询语句使用了条件语句 CASE，对每个学科进行筛选并将对应的分数作为新的列。MAX 函数是为了确保每行只取一个值，以满足 GROUP BY 的要求。

2.3 注意事项

• 行转列会导致查询结果的列增加，因此需要提前确定新列的字段名。
• 如果有多个字段需要转换为列，则需要在查询语句中使用多个 CASE 语句对应不同的字段。
• 使用 GROUP BY 子句对结果进行分组，以保证每行只有一个记录。

3. 列转行（PIVOT）

3.1 原理

列转行，也称为 PIVOT，是将表的列数据转换为行数据的过程。具体而言，对于每一列数据，将其值作为新结果集的一行，并将原表的其他字段与之对应。和行转列类似，列转行也是通过 SQL 查询语句实现的。

3.2 示例

假设我们有以下表 scores，存储了学生的数学和英语成绩：

现在我们想要将每个科目的分数作为新的列，并将原表的其他字段与之对应，得到以下表格：

实现这个转换的 SQL 查询语句如下：

SELECT 
    id, 
    'Math' AS subject,
    math_score AS score
FROM scores
UNION ALL
SELECT
    id,
    'English' AS subject,
    english_score AS score
FROM scores;

上述查询语句使用了 UNION ALL，将两个结果集合并为一个。通过多个 SELECT 子句，在每个子句中设置不同的字段值。这样就可以将列转换为行。

3.3 注意事项

• 列转行会导致查询结果的行增加，因此需要提前确定新行的字段值。
• 使用 UNION ALL 将多个结果集合并为一个，并确保每个结果集的字段名和数据类型一致。

4. 总结

行列转换是在 MySQL 数据表中进行数据处理的常用技术之一。通过行转列可以在表中添加新的列，实现对数据的聚合分析；而列转行可以将列数据展开为行数据，方便进行进一步的数据计算和分析。无论是行转列还是列转行，都可以通过 SQL 查询语句实现。在实际应用中，根据具体需求选择合适的转换方法，并正确使用相应的语法和函数，将有助于提高数据处理的效率和准确性。