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

本文翻译自：How can I return pivot table output in MySQL?

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 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 . 这个想法是， pagecount可以变化，因此输出列的数量应反映出这一点，每个action / pagecount对对应一列，然后每个company_name的命中数。 I'm not sure if this is called a pivot table but someone suggested that? 我不确定这是否称为数据透视表，但有人建议这样做吗？

---

#1楼

参考：https://stackoom.com/question/WCYs/如何在MySQL中返回数据透视表输出

---

#2楼

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

---

#3楼

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 ;

---

#4楼

For MySQL you can directly put conditions in SUM() function and it will be evaluated as Boolean 0 or 1 and thus you can have your count based on your criteria without using IF/CASE statements 对于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

---

#5楼

For dynamic pivot, use GROUP_CONCAT with CONCAT . 对于动态枢轴，将GROUP_CONCAT与CONCAT使用。 The GROUP_CONCAT function concatenates strings from a group into one string with various options. 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 此处演示

---

#6楼

A stardard-SQL version using boolean logic : 使用布尔逻辑的 stardard-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 NULL yields TRUE . TRUE OR NULL产生TRUE 。
FALSE OR NULL yields NULL . FALSE OR NULL产生NULL 。
NULL OR NULL yields NULL . NULL OR NULL产生NULL 。
And COUNT only counts non-null values. 而COUNT只计算非null值。 Voilá. Voilá。