如果我有一个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
是否可以运行一个MySQL查询来得到这样的输出:
company_name EMAIL PRINT 1 pages PRINT 2 pages PRINT 3 pages
-------------------------------------------------------------
CompanyA 0 0 1 3
CompanyB 1 1 2 0
其思想是pagecount可以变化，因此输出列的数量应该反映这一点，一个列对应每个操作/pagecount对，然后是每个company_name的点击数。我不确定这是否叫做数据透视表但有人建议这样做?
一种选择是结合使用CASE..WHEN语句在MySQL数据库的聚合中是冗余的，并动态考虑所需的查询生成，同时为结果集获得适当的列标题，如下代码块所示:
SET @sql = NULL;
SELECT GROUP_CONCAT(
CONCAT('SUM( `action` = ''', action, '''',pc0,' ) AS ',action,pc1)
)
INTO @sql
FROM
(
SELECT DISTINCT `action`,
IF(`pagecount` IS NULL,'',CONCAT('page',`pagecount`)) AS pc1,
IF(`pagecount` IS NULL,'',CONCAT(' AND `pagecount` = ', pagecount, '')) AS pc0
FROM `tab`
ORDER BY CONCAT(action,pc0)
) t;
SET @sql = CONCAT('SELECT company_name,',@sql,' FROM `tab` GROUP BY company_name');
SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Demo
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 ;
对于动态枢轴，使用GROUP_CONCAT和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;
演示
使用布尔逻辑的标准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;
db < >小提琴
老sqlfiddle
How?
TRUE或NULL产生TRUE。
FALSE或NULL产生NULL。
NULL或NULL产生NULL。
COUNT只计算非空值。瞧。
一种选择是结合使用CASE..WHEN语句在MySQL数据库的聚合中是冗余的，并动态考虑所需的查询生成，同时为结果集获得适当的列标题，如下代码块所示:
SET @sql = NULL;
SELECT GROUP_CONCAT(
CONCAT('SUM( `action` = ''', action, '''',pc0,' ) AS ',action,pc1)
)
INTO @sql
FROM
(
SELECT DISTINCT `action`,
IF(`pagecount` IS NULL,'',CONCAT('page',`pagecount`)) AS pc1,
IF(`pagecount` IS NULL,'',CONCAT(' AND `pagecount` = ', pagecount, '')) AS pc0
FROM `tab`
ORDER BY CONCAT(action,pc0)
) t;
SET @sql = CONCAT('SELECT company_name,',@sql,' FROM `tab` GROUP BY company_name');
SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Demo
