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

How can I return pivot table output in MySQL?

提问by peku

If I have a MySQL table looking something like this:

如果我有一个看起来像这样的 MySQL 表：

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:

是否可以运行 MySQL 查询来获得如下输出：

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 pagecountcan vary so the output column amount should reflect that, one column for each action/pagecountpair and then number of hits per company_name. I'm not sure if this is called a pivot table but someone suggested that?

这个想法是，pagecount可以变化，从而输出列量应反映，对于每个一列action/pagecount每命中对，然后数company_name。我不确定这是否称为数据透视表，但有人建议这样做？

采纳答案by Bjoern

This basically isa pivot table.

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

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

关于如何实现这一点的一个很好的教程可以在这里找到：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.

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

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.

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

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...

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

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

行 ( EMAIL, PRINT x pages) 类似于条件。主要分组是由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.

为了设置条件，这相当CASE大喊使用 -语句。为了按某物分组，好吧，使用 ... GROUP BY。

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

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

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.

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

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/

回答by RRM

My solution is in T-SQL without any pivots:

我的解决方案是在没有任何支点的 T-SQL 中：

SELECT
    CompanyName,  
    SUM(CASE WHEN (action='EMAIL') THEN 1 ELSE 0 END) AS Email,
    SUM(CASE WHEN (action='PRINT' AND pagecount=1) THEN 1 ELSE 0 END) AS Print1Pages,
    SUM(CASE WHEN (action='PRINT' AND pagecount=2) THEN 1 ELSE 0 END) AS Print2Pages,
    SUM(CASE WHEN (action='PRINT' AND pagecount=3) THEN 1 ELSE 0 END) AS Print3Pages
FROM 
    Company
GROUP BY 
    CompanyName

回答by M Khalid Junaid

For MySQL you can directly put conditions in SUM()function and it will be evaluated as Boolean 0or 1and thus you can have your count based on your criteria without using IF/CASEstatements

对于 MySQL，您可以直接将条件放入SUM()函数中，它将被评估为布尔值0或1，因此您可以根据您的条件进行计数，而无需使用IF/CASE语句

SELECT
    company_name,  
    SUM(action = 'EMAIL')AS Email,
    SUM(action = 'PRINT' AND pagecount = 1)AS Print1Pages,
    SUM(action = 'PRINT' AND pagecount = 2)AS Print2Pages,
    SUM(action = 'PRINT' AND pagecount = 3)AS Print3Pages
FROM t
GROUP BY company_name

DEMO

DEMO

回答by Abhishek Gupta

For dynamic pivot, use GROUP_CONCATwith CONCAT. The GROUP_CONCATfunction concatenates strings from a group into one string with various options.

对于动态枢轴，使用GROUP_CONCATwith CONCAT。该GROUP_CONCAT函数连接从一组字符串合并为一个字符串的各种选项。

SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN action = "',
      action,'"  AND ', 
           (CASE WHEN pagecount IS NOT NULL 
           THEN CONCAT("pagecount = ",pagecount) 
           ELSE pagecount IS NULL END),
      ' THEN 1 ELSE 0 end) AS ',
      action, IFNULL(pagecount,'')

    )
  )
INTO @sql
FROM
  t;

SET @sql = CONCAT('SELECT company_name, ', @sql, ' 
                  FROM t 
                   GROUP BY company_name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

DEMO HERE

演示在这里

回答by Erwin Brandstetter

A stardard-SQLversion using boolean logic:

一个参考标准-SQL使用的版本布尔逻辑：

SELECT company_name
     , COUNT(action = 'EMAIL' OR NULL) AS "Email"
     , COUNT(action = 'PRINT' AND pagecount = 1 OR NULL) AS "Print 1 pages"
     , COUNT(action = 'PRINT' AND pagecount = 2 OR NULL) AS "Print 2 pages"
     , COUNT(action = 'PRINT' AND pagecount = 3 OR NULL) AS "Print 3 pages"
FROM   tbl
GROUP  BY company_name;

SQL Fiddle.

SQL小提琴。

How?

如何？

TRUE OR NULLyields TRUE.
FALSE OR NULLyields NULL.
NULL OR NULLyields NULL.
And COUNTonly counts non-null values. Voilá.

TRUE OR NULL产量TRUE。
FALSE OR NULL产量NULL。
NULL OR NULL产量NULL。
并且COUNT只计算非空值。瞧。

回答by Talha

Correct answer is:

正确答案是：

select table_record_id,
group_concat(if(value_name='note', value_text, NULL)) as note
,group_concat(if(value_name='hire_date', value_text, NULL)) as hire_date
,group_concat(if(value_name='termination_date', value_text, NULL)) as termination_date
,group_concat(if(value_name='department', value_text, NULL)) as department
,group_concat(if(value_name='reporting_to', value_text, NULL)) as reporting_to
,group_concat(if(value_name='shift_start_time', value_text, NULL)) as shift_start_time
,group_concat(if(value_name='shift_end_time', value_text, NULL)) as shift_end_time
from other_value
where table_name = 'employee'
and is_active = 'y'
and is_deleted = 'n'
GROUP BY table_record_id

回答by Peter Green

There is a tool called MySQL Pivot table generator, it can help you create web based pivot table that you can later export to excel(if you like). it can work if your data is in a single table or in several tables .

有一个名为 MySQL 数据透视表生成器的工具，它可以帮助您创建基于 Web 的数据透视表，您可以稍后将其导出到 excel（如果您愿意）。如果您的数据在单个表或多个表中，它可以工作。

All you need to do is to specify the data source of the columns (it supports dynamic columns), rows , the values in the body of the table and table relationship (if there are any)

您需要做的就是指定列的数据源（它支持动态列）、行、表体中的值和表关系（如果有的话）

The home page of this tool is http://mysqlpivottable.net

这个工具的主页是http://mysqlpivottable.net

回答by irba

select t3.name, sum(t3.prod_A) as Prod_A, sum(t3.prod_B) as Prod_B, sum(t3.prod_C) as    Prod_C, sum(t3.prod_D) as Prod_D, sum(t3.prod_E) as Prod_E  
from
(select t2.name as name, 
case when t2.prodid = 1 then t2.counts
else 0 end  prod_A, 

case when t2.prodid = 2 then t2.counts
else 0 end prod_B,

case when t2.prodid = 3 then t2.counts
else 0 end prod_C,

case when t2.prodid = 4 then t2.counts
else 0 end prod_D, 

case when t2.prodid = "5" then t2.counts
else 0 end prod_E

from 
(SELECT partners.name as name, sales.products_id as prodid, count(products.name) as counts
FROM test.sales left outer join test.partners on sales.partners_id = partners.id
left outer join test.products on sales.products_id = products.id 
where sales.partners_id = partners.id and sales.products_id = products.id group by partners.name, prodid) t2) t3

group by t3.name ;