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

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

关于如何实现这一点的一个很好的教程可以在这里找到:http://www.artfulsoftware.com/infotree/qrytip.php?id=78

我建议你阅读这篇文章，并根据你的需要调整这个解决方案。

< em > < / em >更新

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

也许这个链接很快就会回来，我会留意的。

电子表格的方式…

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

但是…这不是问题所在，它甚至可能导致一些缺点，比如如何将数据输入电子表格，有问题的缩放等等。

SQL的方式…

假设他的表格是这样的:

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;

现在看看他/她想要的桌子:

company_name    EMAIL   PRINT 1 pages   PRINT 2 pages   PRINT 3 pages
-------------------------------------------------------------
CompanyA        0       0               1               3
CompanyB        1       1               2               0

行(EMAIL， PRINT x pages)类似于条件。主要的分组是通过company_name。

为了设置条件，这里需要使用__abc2 -语句。为了根据某些东西进行分组，嗯，使用…GROUP BY。

提供这个枢轴的基本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`;

这将很快提供所需的结果。这种方法的主要缺点是，数据透视表中的行越多，需要在SQL语句中定义的条件就越多。

这也是可以处理的，因此人们倾向于使用准备好的语句、例程、计数器等。

关于这个主题的其他链接:

• 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/

我的解决方案是在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

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 ;

对于MySQL，你可以直接在SUM()函数中放入条件，它将是计算为布尔值__ABC1或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

< a href = " http://sqlfiddle.com/ !2/60c93/1 " > < >强DEMO < /强> < / >

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;

< a href = " http://sqlfiddle.com/ !2/60c93/112" rel="nofollow noreferrer">DEMO HERE .

使用布尔逻辑的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;

有一个叫做MySQL数据透视表生成器的工具，它可以帮助你创建一个基于web的数据透视表，然后你可以导出到excel(如果你喜欢的话)。如果数据在单个表或多个表中，它都可以工作。

这个工具的主页是https://mysqlreports.com/mysql-reporting-tools/mysql-pivot-table/

正确答案是:

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

一个选项是在MySQL数据库的聚合中使用CASE..WHEN语句是多余的，并考虑所需的动态生成查询和获得正确的列标题作为结果集，如下面的代码块所示:

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 company_name, 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 "print 1 PAGE",
SUM(CASE WHEN ACTION = 'Print' AND pagecount = 2 THEN 1 ELSE 0 END) AS "print 2 PAGE",
SUM(CASE WHEN ACTION = 'Print' AND pagecount = 3 THEN 1 ELSE 0 END) AS "print 2 PAGE"
FROM test1 GROUP BY company_name;