如何在MySQL中返回数据透视表输出? [英] How can I return pivot table output in MySQL?

问题描述

如果我有一个看起来像这样的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

是否可以运行MySQL查询以获取如下输出:

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

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

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.

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

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

在上面的链接当前不可用之后，我不得不为在此搜索mysql枢纽答案的所有人提供一些其他信息.它确实具有大量信息，我不会在这里放置所有内容(甚至因为我不想复制他们的大量知识而在此处提供更多内容)，但是我将提供一些有关如何处理数据透视的建议通常以peku的示例首先列出问题的方式列出sql方式.

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.

电子表格方式...

许多人为此使用了诸如MSExcel，OpenOffice或其他电子表格工具之类的工具.这是一个有效的解决方案，只需将数据复制到那里，然后使用GUI提供的工具即可解决此问题.

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.

SQL方式...

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

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

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

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

为了设置条件，我们大声喊着使用 CASE 语句.为了对某物进行分组，请使用... GROUP BY.

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.

提供此枢纽的基本SQL看起来可能像这样:

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`;

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

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/

• 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/

这篇关于如何在MySQL中返回数据透视表输出?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！