迹忆客 专注技术分享

在本文中，我们将介绍如何将表数据从行转换为列。这个过程称为旋转。这种转换的结果通常是一个汇总表，我们在其中提供适合报告生成的信息。

在 MySQL 中，没有创建数据透视表的内置函数，因此你必须编写一个 MySQL 查询来生成数据透视表。幸运的是，有三种不同的方法可以使用 MySQL 创建数据透视表。

以下脚本创建一个包含三列（name、subjectid 和 marks）的表 student。

create table student(name varchar(20),subjectid int(10), marks int(10));

在这里，我们将样本数据值插入到学生表中以进行演示。

insert into student values ('Sam',1,70);
insert into student values ('Sam',2,77);
insert into student values ('Sam',3,71);
insert into student values ('Sam',4,70);
insert into student values ('Sam',1,99);
insert into student values ('John',1,89);
insert into student values ('John',2,87);
insert into student values ('John',3,88);
insert into student values ('John',4,89); 
insert into student values ('Martin',1,60);
insert into student values ('Martin',2,47);
insert into student values ('Martin',3,68);
insert into student values ('Martin',4,39);

插入这些值后，让我们编写一个 select 查询来查看数据库中的记录。

Select * from student;

输出将是：

在 MySQL 中使用 MAX 函数和 IF 语句创建数据透视表

这是解决问题的非常直接的方法。在这种方法中，我们将使用 MAX 函数和 IF 语句。如果数据库中某个学生的单个科目有多个条目，则最多需要两分。

现在让我们看看如何在 SELECT 查询中使用它：

SELECT name,
MAX(IF(subjectid=1, marks, NULL)) AS Sub1,
MAX(IF(subjectid=2, marks, NULL)) AS Sub2,
MAX(IF(subjectid=3, marks, NULL)) AS Sub3,
MAX(IF(subjectid=4, marks, NULL)) AS Sub4
FROM student
GROUP BY name;

输出将是：

该输出是针对每个学生的学科的编译结果。由于你希望每个学生占一行，因此你需要按姓名列进行分组。此外，你需要为每一列指定一个条件，即每个主题一个条件。

在 MySQL 中使用 CASE 语句创建数据透视表

这也是解决问题的直接方法。在这种方法中，我们将使用 MAX 函数和 CASE 语句。此外，如果数据库中的某个学生存在多个针对单个学科的条目，则最多需要两分。同样，如果某些学生的分数不存在，则取 NULL 值。

现在让我们看看如何在 SELECT 查询中使用它：

SELECT name,
MAX(CASE WHEN subjectid=1 THEN marks ELSE NULL END) AS Sub1,
MAX(CASE WHEN subjectid=2 THEN marks ELSE NULL END) AS Sub2,
MAX(CASE WHEN subjectid=3 THEN marks ELSE NULL END) AS Sub3,
MAX(CASE WHEN subjectid=4 THEN marks ELSE NULL END) AS Sub4
FROM student
GROUP BY name; 

输出将是：

在 MySQL 中使用动态数据透视列创建数据透视表

聚合函数 (MAX)、IF 语句和 CASE 语句生成了上述示例中的数据透视表。使用这种方法的缺点是我们在编写查询时需要知道列标题，当列数增加时，代码也会增加。我们可以采用上述两种方法来获得较小的结果和所有可能的值。

因此，为了克服这些限制，我们可以使用动态枢轴列。在这里，GROUP_CONCAT 函数可以动态生成 PIVOT 表输出的列。

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT CONCAT(
 ' MAX(CASE WHEN subjectid = ', subjectid, ' THEN marks ELSE 0 END) 
 AS "', subjectid, '"')
)
INTO @sql FROM student;

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

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

在 GROUP_CONCAT 函数中，我们使用 DISTINCT 关键字来获取唯一的标记列表。否则，我们的查询会因为列表太长而出错。你可以随时编写 Select * from @sql 来检查动态查询。

现在，当我们执行这个查询时，这就是我们得到的结果：

请注意 ，列标题是根据表中的值动态生成的，并且列标题表示 subjectid。