MySQL 中的数据透视表 |
您所在的位置：网站首页 › 怎么样创建数据透视表 › MySQL 中的数据透视表 |
|
在 MySQL 中使用 MAX 函数和 IF 语句创建数据透视表
在 MySQL 中使用 CASE 语句创建数据透视表
在 MySQL 中使用动态数据透视列创建数据透视表
在本文中，我们将介绍如何将表数据从行转换为列。这个过程称为旋转。这种转换的结果通常是一个汇总表，我们在其中提供适合报告生成的信息。 在 MySQL 中，没有创建数据透视表的内置函数，因此你必须编写一个 MySQL 查询来生成数据透视表。幸运的是，有三种不同的方法可以使用 MySQL 创建数据透视表。 在 MySQL 中使用 IF 语句创建数据透视表 使用 CASE 语句在 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。 |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |