本文翻译自: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;
#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;
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á。