如何在 MySQL 中返回数据透视表输出?

2021-11-20 00:00:00 pivot sql mysql

如果我有一个像这样的 MySQL 表:

<前>company_name 操作页数-------------------------------公司 A 打印 3公司 A 打印 2公司 A 打印 3B 公司 EMAILB 公司打印 2B 公司打印 2B 公司打印 1公司 A 打印 3

是否可以运行 MySQL 查询来获得这样的输出:

<前>company_name EMAIL PRINT 1 pages PRINT 2 pages PRINT 3 pages-------------------------------------------------------------公司A 0 0 1 3公司B 1 1 2 0

这个想法是 pagecount 可以变化，所以输出列的数量应该反映这一点，每个 action/pagecount 对一列，然后每个 company_name 的点击次数.我不确定这是否称为数据透视表，但有人建议这样做?

解决方案

这基本上是一个数据透视表.

可以在此处找到有关如何实现此目标的不错教程:http://www.artfulsoftware.com/infotree/qrytip.php?id=78

我建议您阅读这篇文章并根据您的需求调整此解决方案.

更新

在上面的链接目前不再可用之后，我觉得有必要为所有在这里搜索 mysql 数据透视答案的人提供一些额外的信息.它确实有大量的信息，我不会把所有的东西都放在这里(甚至更多，因为我只是不想复制他们的丰富知识)，但我会就如何处理pivot提供一些建议以最先提出问题的peku为例，通常以sql方式表.

也许链接很快就会回来，我会留意的.

电子表格方式...

为此目的，许多人只是使用 MSExcel、OpenOffice 或其他电子表格工具等工具.这是一个有效的解决方案，只需将数据复制到那里并使用 GUI 提供的工具来解决这个问题.

但是……这不是问题，它甚至可能导致一些缺点，例如如何将数据导入电子表格、有问题的缩放等等.

SQL 方式...

鉴于他的桌子看起来像这样:

创建表`test_pivot`(`pid` bigint(20) NOT NULL AUTO_INCREMENT,`company_name` varchar(32) 默认为空，`action` varchar(16) 默认为空，`pagecount` bigint(20) 默认为空，主键(`pid`)) 引擎=MyISAM;

现在查看他/她想要的表格:

company_name EMAIL PRINT 1 pages PRINT 2 pages PRINT 3 pages-------------------------------------------------------------公司A 0 0 1 3公司B 1 1 2 0

行(EMAIL、PRINT x pages)类似于条件.主要分组是按company_name.

为了设置条件，使用CASE-语句.为了按某些内容分组，请使用 ... GROUP BY.

提供此数据透视表的基本 SQL 可能如下所示:

SELECT P.`company_name`,数数(案件WHEN P.`action`='EMAIL'那么 1否则为空结尾) 作为电子邮件"，数数(案件当 P.`action`='PRINT' AND P.`pagecount` = '1'THEN P.`pagecount`否则为空结尾) 作为打印 1 页"，数数(案件当 P.`action`='PRINT' AND P.`pagecount` = '2'THEN P.`pagecount`否则为空结尾) 作为打印 2 页"，数数(案件当 P.`action`='PRINT' AND P.`pagecount` = '3'THEN P.`pagecount`否则为空结尾) AS '打印 3 页'FROM test_pivot PGROUP BY P.`company_name`;

这应该会非常快地提供所需的结果.这种方法的主要缺点是，您希望数据透视表中的行越多，您需要在 SQL 语句中定义的条件就越多.

这也可以解决，因此人们倾向于使用准备好的语句、例程、计数器等.

有关此主题的一些其他链接:

http://anothermysqldba.blogspot.de/2013/06/pivot-tables-example-in-mysql.html
http://www.codeproject.com/文章/363339/Cross-Tabulation-Pivot-Tables-with-MySQL
http://datacharmer.org/downloads/pivot_tables_mysql_5.pdf
https://codingsight.com/pivot-tables-in-mysql/

If I have a MySQL table looking something like this:

company_name    action  pagecount
-------------------------------
Company A       PRINT   3
Company A       PRINT   2
Company A       PRINT   3
Company B       EMAIL   
Company B       PRINT   2
Company B       PRINT   2
Company B       PRINT   1
Company A       PRINT   3

Is it possible to run a MySQL query to get output like this:

company_name    EMAIL   PRINT 1 pages   PRINT 2 pages   PRINT 3 pages
-------------------------------------------------------------
CompanyA        0       0               1               3
CompanyB        1       1               2               0

The idea is that pagecount can vary so the output column amount should reflect that, one column for each action/pagecount pair and then number of hits per company_name. I'm not sure if this is called a pivot table but someone suggested that?

解决方案

This basically is a pivot table.

A nice tutorial on how to achieve this can be found here: http://www.artfulsoftware.com/infotree/qrytip.php?id=78

I advise reading this post and adapt this solution to your needs.

Update

After the link above is currently not available any longer I feel obliged to provide some additional information for all of you searching for mysql pivot answers in here. It really had a vast amount of information, and I won't put everything from there in here (even more since I just don't want to copy their vast knowledge), but I'll give some advice on how to deal with pivot tables the sql way generally with the example from peku who asked the question in the first place.

Maybe the link comes back soon, I'll keep an eye out for it.

The spreadsheet way...

Many people just use a tool like MSExcel, OpenOffice or other spreadsheet-tools for this purpose. This is a valid solution, just copy the data over there and use the tools the GUI offer to solve this.

But... this wasn't the question, and it might even lead to some disadvantages, like how to get the data into the spreadsheet, problematic scaling and so on.

The SQL way...

Given his table looks something like this:

CREATE TABLE `test_pivot` (
  `pid` bigint(20) NOT NULL AUTO_INCREMENT,
  `company_name` varchar(32) DEFAULT NULL,
  `action` varchar(16) DEFAULT NULL,
  `pagecount` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`pid`)
) ENGINE=MyISAM;

Now look into his/her desired table:

company_name    EMAIL   PRINT 1 pages   PRINT 2 pages   PRINT 3 pages
-------------------------------------------------------------
CompanyA        0       0               1               3
CompanyB        1       1               2               0

The rows (EMAIL, PRINT x pages) resemble conditions. The main grouping is by company_name.

In order to set up the conditions this rather shouts for using the CASE-statement. In order to group by something, well, use ... GROUP BY.

The basic SQL providing this pivot can look something like this:

SELECT  P.`company_name`,
    COUNT(
        CASE 
            WHEN P.`action`='EMAIL' 
            THEN 1 
            ELSE NULL 
        END
    ) AS 'EMAIL',
    COUNT(
        CASE 
            WHEN P.`action`='PRINT' AND P.`pagecount` = '1' 
            THEN P.`pagecount` 
            ELSE NULL 
        END
    ) AS 'PRINT 1 pages',
    COUNT(
        CASE 
            WHEN P.`action`='PRINT' AND P.`pagecount` = '2' 
            THEN P.`pagecount` 
            ELSE NULL 
        END
    ) AS 'PRINT 2 pages',
    COUNT(
        CASE 
            WHEN P.`action`='PRINT' AND P.`pagecount` = '3' 
            THEN P.`pagecount` 
            ELSE NULL 
        END
    ) AS 'PRINT 3 pages'
FROM    test_pivot P
GROUP BY P.`company_name`;

This should provide the desired result very fast. The major downside for this approach, the more rows you want in your pivot table, the more conditions you need to define in your SQL statement.

This can be dealt with, too, therefore people tend to use prepared statements, routines, counters and such.

Some additional links about this topic:

http://anothermysqldba.blogspot.de/2013/06/pivot-tables-example-in-mysql.html
http://www.codeproject.com/Articles/363339/Cross-Tabulation-Pivot-Tables-with-MySQL
http://datacharmer.org/downloads/pivot_tables_mysql_5.pdf
https://codingsight.com/pivot-tables-in-mysql/

相关文章