篇首语：本文由小编为大家整理，主要介绍了mysql中怎么做pivot相关的知识，希望对你有一定的参考价值。
mysql中怎么做pivot，行列转换的问题。例如有一个学生成绩表，内容如第一张图。如果想统计每个班的各科最高分,怎么做，怎么才能得到第二张图的结果。
没在mysql中试过，不过最近在使用集算器，可以给楼主做下参考。
首先，不是所有的数据库都提供 pivot；其次，就算所有的数据库都提供 pivot，但如果是汇总了多个数据库的数据后还想再来个 pivot？那还是要用到集算器的 pivot。
下面我们来看集算器的 pivot 如何使用
代码说明：
A1：第一步连接数据库
A2：第二步提取数据做预处理 (这一步可进一步扩展为做汇总或聚合等复杂的计算，具体方法请参考相关文章)
A3：第三步即实现 pivot 的列转行功能并呈现出来
目前测试的mysql版本不支持（mysql Ver 14.14 Distrib 5.1.61）
对应转换（注意关键字sum，缺少则除english外，其他分值为0；如果存在多个重复分值，结果则是错误）
参考技术B "pivot table" or a "crosstab report"(Note: this page needs to be wikified)
SQL Characteristic Functions: Do it without "if", "case", or "GROUP_CONCAT".
Yes, there is use for this..."if" statements sometimes cause problems
when used in combination.
The simple secret, and it"s also why they work in almost all databases, is the
following functions:
o sign (x) returns -1,0, +1 for values x < 0, x = 0, x > 0 respectively
o abs( sign( x) ) returns 0 if x = 0 else, 1 if x > 0 or x < 0
o 1-abs( sign( x) ) complement of the above, since this returns 1 only if x = 0
Quick example: sign(-1) = -1, abs( sign(-1) ) = 1, 1-abs( sign(-1) ) = 0
Data for full example:
CREATE TABLE exams (
pkey int(11) NOT NULL auto_increment,
name varchar(15),
exam int,
score int,
PRIMARY KEY (pkey)
);
insert into exams (name,exam,score) values ("Bob",1,75);
insert into exams (name,exam,score) values ("Bob",2,77);
insert into exams (name,exam,score) values ("Bob",3,78);
insert into exams (name,exam,score) values ("Bob",4,80);
insert into exams (name,exam,score) values ("Sue",1,90);
insert into exams (name,exam,score) values ("Sue",2,97);
insert into exams (name,exam,score) values ("Sue",3,98);
insert into exams (name,exam,score) values ("Sue",4,99);
mysql> select * from exams;
+------+------+------+-------+
| pkey | name | exam | score |
+------+------+------+-------+
| 1 | Bob | 1 | 75 |
| 2 | Bob | 2 | 77 |
| 3 | Bob | 3 | 78 |
| 4 | Bob | 4 | 80 |
| 5 | Sue | 1 | 90 |
| 6 | Sue | 2 | 97 |
| 7 | Sue | 3 | 98 |
| 8 | Sue | 4 | 99 |
+------+------+------+-------+
8 rows in set (0.00 sec)
mysql> select name,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4
from exams group by name;
+------+-------+-------+-------+-------+
| name | exam1 | exam2 | exam3 | exam4 |
+------+-------+-------+-------+-------+
| Bob | 75 | 77 | 78 | 80 |
| Sue | 90 | 97 | 98 | 99 |
+------+-------+-------+-------+-------+
2 rows in set (0.00 sec)
Note, the above pivot table was created with one select statement.
Let"s decompose to make the trick clearer, for the second exam:
mysql> select name, score, exam, exam-2, sign(exam-2), abs(sign(exam-2)), 1-abs(sign(exam-2)),
score*(1-abs(sign(exam-2))) as exam2 from exams;
+------+-------+------+--------+--------------+-------------------+---------------------+-------+
| name | score | exam | exam-2 | sign(exam-2) | abs(sign(exam-2)) | 1-abs(sign(exam-2)) | exam2 |
+------+-------+------+--------+--------------+-------------------+---------------------+-------+
| Bob | 75 | 1 | -1 | -1 | 1 | 0 | 0 |
| Bob | 77 | 2 | 0 | 0 | 0 | 1 | 77 |
| Bob | 78 | 3 | 1 | 1 | 1 | 0 | 0 |
| Bob | 80 | 4 | 2 | 1 | 1 | 0 | 0 |
| Sue | 90 | 1 | -1 | -1 | 1 | 0 | 0 |
| Sue | 97 | 2 | 0 | 0 | 0 | 1 | 97 |
| Sue | 98 | 3 | 1 | 1 | 1 | 0 | 0 |
| Sue | 99 | 4 | 2 | 1 | 1 | 0 | 0 |
+------+-------+------+--------+--------------+-------------------+---------------------+-------+
8 rows in set (0.00 sec)
You may think IF"s would be clean but WATCH OUT!
Look what the following gives (INCORRECT !!):
在mysql中使用pivot函数来制作一个先验表
【中文标题】在mysql中使用pivot函数来制作一个先验表【英文标题】：using pivot function in mysql to make a table apriori 【发布时间】：2020-06-30 07:18:55 【问题描述】：在这种情况下MySQL pivot row into dynamic number of columns 几乎是相同的情况，但结果不同，这让我感到困惑。
假设我有 3 张桌子
create table order_match(id int(10) PRIMARY KEY not null,order_status_id int(10) not null);create table order_match_detail( id int(10) PRIMARY KEY not null, order_match_id int(10) not null, product_id int(10) NOT NULL);create table product(id int(10) PRIMARY KEY not null,name varchar(255) not null);Insert into order_match (id, order_status_id)select 1, 6 union allselect 2, 7 union allselect 3, 6 union allselect 4, 6;Insert into order_match_detail (id, order_match_id, product_id)select 1, 1, 147 union allselect 2, 2, 148 union allselect 3, 3, 147 union allselect 4, 4, 149 union allselect 5, 4, 147;Insert into product (id, name)select 147, "orange" union allselect 148, "carrot" union allselect 149, "Apple";order_match.id = order_match_detail.order_match_id和order_match_detail.product_id = product.id
所以就像MySQL pivot row into dynamic number of columns 中的前一个案例一样，我想在 order_status_id 中输入产品名称而不是 7 中的交易（因为 7 是过期交易并被拒绝）
the expected results was like this :id (in order_match) | Orange | Carrot | Apple1 1 0 03 1 0 04 1 0 1 基于之前案例的解决方案，我使用了这个
SET @sql = NULL;SELECT GROUP_CONCAT(DISTINCT CONCAT( "count(case when product.name = """, product.name, """ then 1 end) AS ", replace(product.name, " ", "") ) ) INTO @sqlfrom product;SET @sql = CONCAT("SELECT omd.order_match_id, ", @sql, " from order_match_detail omdleft join order_match om on omd.order_match_id = om.idleft join product p on omd.product_id = p.idwhere om.order_status_id in (4, 5, 6, 8)group by omd.order_match_id");PREPARE stmt FROM @sql;EXECUTE stmt;但是我想知道为什么返回 0 是不可能的
这是小提琴https://www.db-fiddle.com/f/nDe3oQ3VdtfS5QDokieHN4/6
【问题讨论】：
正如已经不止一次提到的那样：meta.***.com/questions/333952/…。也就是说，您最好的选择（同样如前所述）是处理应用程序代码中的数据显示问题 对不起先生，先生，@Strawberry 先生 将SELECT @sql; 添加到您的小提琴中。执行。然后复制它的FROM 部分，添加SELECT * 并执行。调查结果。 idk 为什么返回 0 因为sales 中没有 partner_id=2 和 product_id=1 的行。 对不起先生，这是错误的小提琴 【参考方案1】： 对于您的 GROUP_CONCAT 查询；在您的情况下 stmt，您将您的产品表称为 product 本身。但是在您的联接查询中，您将产品表称为别名p。由于第一个 group_concat 查询是连接查询的一部分，因此您需要保持表别名相同。（在第 5 行进行了更改）
SET @sql = NULL;SELECT GROUP_CONCAT(DISTINCT CONCAT( "count(case when p.name = """, product.name, """ then 1 end) AS ", replace(product.name, " ", "") ) ) INTO @pivotsqlfrom product;SET @sql = CONCAT("SELECT omd.order_match_id, ", @pivotsql, " from order_match_detail omdleft join order_match om on omd.order_match_id = om.idleft join product p on omd.product_id = p.id where om.order_status_id in (4, 5, 6, 8)group by omd.order_match_id");PREPARE stmt FROM @sql;EXECUTE stmt;【讨论】：
以上是关于mysql中怎么做pivot的主要内容，如果未能解决你的问题，请参考以下文章
