MySQL 动态行转列

1. 介绍

在数据库中，通常情况下数据是以行的形式来存储和处理的，每一行代表一个实体或记录。然而，在某些情况下，我们希望将行数据转换为列数据，即所谓的动态行转列。MySQL作为一种常用的关系型数据库，提供了一些方法来实现动态行转列的功能。

动态行转列通常在以下情况下使用：

当我们需要将一行数据中的多个属性或字段转换为多列时。
当我们需要对数据进行透视或聚合操作时，可以将行数据转换为列数据以便于分析和计算。

在本文中，我们将详细讨论MySQL中动态行转列的几种实现方式和示例。

2. 实现方式

2.1 使用CASE语句

在MySQL中，可以使用CASE语句来实现动态行转列的功能。通过CASE语句，我们可以根据条件选择不同的列作为结果的一部分。

下面是一个简单的示例，假设我们有一个名为student的表，包含id、name和grade字段，现在我们希望将这些数据以name为列转换：

SELECT 
    id,
    MAX(CASE WHEN name = 'Alice' THEN grade END) AS 'Alice',
    MAX(CASE WHEN name = 'Bob' THEN grade END) AS 'Bob',
    MAX(CASE WHEN name = 'Charlie' THEN grade END) AS 'Charlie'
FROM student
GROUP BY id;

执行以上查询语句后，将得到动态行转列后的结果，其中每个学生的姓名为列名，对应的成绩为值。

2.2 使用GROUP_CONCAT函数

另一种实现动态行转列的方法是使用MySQL的GROUP_CONCAT函数。GROUP_CONCAT函数可以将多行数据按照指定的分隔符连接为一个字符串，因此可以将一组行数据转换为一列数据。

以下示例假设我们的student表包含id、name和grade字段，现在我们希望将每个学生的成绩以逗号分隔的形式列出：

SELECT 
    id,
    GROUP_CONCAT(grade SEPARATOR ', ') AS grades
FROM student
GROUP BY id;

执行以上查询语句后，将得到动态行转列后的结果，其中每个学生的ID为一列，对应的成绩以逗号分隔的形式列出。

2.3 使用PIVOT表达式

在MySQL 8.0版及以上版本中，引入了PIVOT表达式，可以更方便地实现动态行转列的功能。PIVOT表达式基于标准SQL的PIVOT操作实现，可以将某列的值转换为列名，并将其对应的值作为新的列。

以下是一个示例，假设我们有一个名为sales的表，包含product、year和amount字段，现在我们希望将year作为列名，每一年的销售额作为新的列：

SELECT 
    product,
    COALESCE(`2018`, 0) AS '2018',
    COALESCE(`2019`, 0) AS '2019',
    COALESCE(`2020`, 0) AS '2020'
FROM (
    SELECT product, year, amount
    FROM sales
) AS src
PIVOT (
    SUM(amount)
    FOR year IN (`2018`, `2019`, `2020`)
) AS pvt;

执行以上查询语句后，将得到动态行转列后的结果，其中每个产品的名称为一列，对应的年份及销售额为值。

3. 示例代码

为了更好地理解和实践MySQL动态行转列的方法，以下示例提供了使用student表来演示动态行转列的代码。

3.1 创建示例表

首先，我们需要创建一个名为student的示例表来存储学生的成绩信息，表结构如下：

CREATE TABLE student (
    id INT,
    name VARCHAR(100),
    grade INT
);

然后，插入一些示例数据到表中：

INSERT INTO student (id, name, grade) VALUES
    (1, 'Alice', 90),
    (1, 'Bob', 85),
    (2, 'Alice', 95),
    (2, 'Charlie', 80),
    (3, 'Bob', 75),
    (3, 'Charlie', 70);

3.2 使用CASE语句进行动态行转列

下面是使用CASE语句实现动态行转列的示例代码，将学生的成绩按照姓名转换为列：

SELECT
    id,
    MAX(CASE WHEN name = 'Alice' THEN grade END) AS 'Alice',
    MAX(CASE WHEN name = 'Bob' THEN grade END) AS 'Bob',
    MAX(CASE WHEN name = 'Charlie' THEN grade END) AS 'Charlie'
FROM student
GROUP BY id;

运行以上代码后，将得到以下结果：

+----+-------+------+---------+
| id | Alice | Bob  | Charlie |
+----+-------+------+---------+
|  1 |    90 |   85 |    NULL |
|  2 |    95 | NULL |      80 |
|  3 |  NULL |   75 |      70 |
+----+-------+------+---------+

3.3 使用GROUP_CONCAT函数进行动态行转列

以下是使用GROUP_CONCAT函数实现动态行转列的示例代码，将每个学生的成绩以逗号分隔的方式列出：

SELECT
    id,
    GROUP_CONCAT(grade SEPARATOR ', ') AS grades
FROM student
GROUP BY id;

运行以上代码后，将得到以下结果：

+----+--------+
| id | grades |
+----+--------+
|  1 | 90, 85 |
|  2 | 95, 80 |
|  3 | 75, 70 |
+----+--------+

3.4 使用PIVOT表达式进行动态行转列

以下是使用PIVOT表达式实现动态行转列的示例代码，将不同年份的销售额按照产品进行列转换：

SELECT
    product,
    COALESCE(`2018`, 0) AS '2018',
    COALESCE(`2019`, 0) AS '2019',
    COALESCE(`2020`, 0) AS '2020'
FROM (
    SELECT product, year, amount
    FROM sales
) AS src
PIVOT (
    SUM(amount)
    FOR year IN (`2018`, `2019`, `2020`)
) AS pvt;