MySQL 中的数据透视表

Sweety Rupani 2024年2月15日
  1. 在 MySQL 中使用 MAX 函数和 IF 语句创建数据透视表
  2. 在 MySQL 中使用 CASE 语句创建数据透视表
  3. 在 MySQL 中使用动态数据透视列创建数据透视表
MySQL 中的数据透视表

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

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

  1. 在 MySQL 中使用 IF 语句创建数据透视表
  2. 使用 CASE 语句在 MySQL 中创建数据透视表
  3. 使用动态数据透视列在 MySQL 中创建数据透视表

以下脚本创建一个包含三列(namesubjectidmarks)的表 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 中的数据透视表 - 示例

在 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 中的数据透视表-if 语句

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

在 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 中的数据透视表-case 语句

在 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 来检查动态查询。

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

mysql 中的数据透视表 - 动态数据透视列

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

相关文章 - MySQL Table