MySQL动态行转列|极客教程

MySQL动态行转列

在实际的数据库应用中，有时候我们需要将数据库中的行数据按照指定的列进行转换，这种操作通常被称为行转列。MySQL并没有提供专门的函数来实现行转列的操作，但是我们可以通过一些技巧和方法来实现。

实现动态行转列

假设有如下的表结构和数据：

CREATE TABLE student (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    class INT,
    subject VARCHAR(50),
    score INT
);

INSERT INTO student (id, name, class, subject, score) VALUES
(1, 'Alice', 1, 'Math', 90),
(2, 'Bob', 1, 'Math', 85),
(3, 'Alice', 1, 'English', 95),
(4, 'Bob', 1, 'English', 88);

现在我们希望将上面的数据按照姓名和科目进行转换，将每个学生的数学和英语成绩分别展示在单独的列中。可以通过以下的SQL语句来实现：

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

这里使用了CASE WHEN语句来判断每个学生的成绩应该属于哪个科目，并将对应的成绩放在相应的列中。最后使用GROUP BY按照姓名进行分组。

动态行转列

上面的示例中，我们已经知道了学生的科目是固定的，但是在实际情况中，可能存在科目会动态增加的情况。这时候我们可以通过动态的方式来实现行转列。

首先，我们需要编写一个存储过程来动态生成SQL语句，然后执行生成的SQL语句来实现动态行转列。以下是一个示例的存储过程：

DELIMITER //

CREATE PROCEDURE dynamic_pivot()
BEGIN
    SET SESSION group_concat_max_len = 1000000;

    SELECT GROUP_CONCAT(
        CONCAT(
            'MAX(CASE WHEN subject = ', 
            '''', subject, ''', ',
            'THEN score END) AS ', 
            subject
        )
    )
    INTO @pivot_columns
    FROM (
        SELECT DISTINCT subject
        FROM student
    ) AS subjects;

    SET @sql = CONCAT(
        'SELECT name, ',
        @pivot_columns,
        ' FROM student GROUP BY name;'
    );

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

DELIMITER ;

在上面的存储过程中，首先查询出所有不同的科目，然后使用GROUP_CONCAT函数将生成动态的列，并拼接成动态的SQL语句。最后使用PREPARE和EXECUTE来执行SQL语句。

运行结果

下面是运行上面的存储过程的结果：

CALL dynamic_pivot();

运行结果如下：

+-------+-----------+--------------+
| name  | Math      | English      |
+-------+-----------+--------------+
| Alice | 90        | 95           |
| Bob   | 85        | 88           |
+-------+-----------+--------------+

通过上面的执行结果可以看出，动态行转列已经成功实现，每个学生的数学和英语成绩已经被转换为独立的列。