如何在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

or

1

and thus you can have your count based on your criteria without using

IF/CASE

statements

對于MySQL，您可以直接将條件放在

SUM()

函數中，并将其評估為布爾值 或

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á。