如何返回MySQL中的枢轴表输出？

Question

如果我有一张MySQL桌子看起来像这样：

Company_name	Action	PageCount
Company A	Print	3
Company A	Print	2
Company A	Print	3
Company B	Email
B Print 2 Company B	Print	2
Company B	Print	2
Company B Print 2 Company B	Print	1
Company 1 Company A	Print	3

可以运行一个mysql查询要以这样的输出：

company_name	电子邮件	打印1页	打印2页	打印3页
companya	0	0	1	3
companyb	1	1	2	0

想法是 pagecount 可以变化，以便输出列数量应反映这一点，每个操作/ pagecount 配对的一列，然后每 company_name 。我不确定这是否称为枢轴表，但有人建议吗？

Answer 1

这基本上是一个枢轴表。

关于如何实现此目的的一个不错的教程可以在此处找到： http：http：// www。 Artfulsoftware.com/infotree/qrytip.php?id=78

我建议阅读此帖子并根据您的需求调整此解决方案。

Update

在当前不再可用之后，我觉得不得不为所有搜索MySQL Pivot答案提供一些其他信息。它确实有大量信息，我不会把所有内容都放在这里（甚至更多，因为我只是不想复制他们的知识），但是我会为如何处理枢轴提供一些建议PEKU的示例通常是SQL的表格，他首先问了这个问题。

也许链接很快就会回来，我会留意它。

电子表格方式...

许多人仅将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> emair> ，打印x页）类似于条件。主要分组是 company_name 。

为了设置条件，这种情况却大喊使用 案例 - 陈述。为了通过某种东西进行分组，请使用... 组组。

提供此枢轴的基本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：// blogspot.de/2013/06/pivot-tables-example-in-mysql.html
• http://www.codeproject.com/articles/363339/363339/cross-tabulation-pabulation-pivot-tables-with-mysql
• http://datacharmer.org/downloads/pivot_tables_mysql_5.pdf
• mysql/“ rel =“ noreferrer”> https://codingsight.com/pivot-tables-in-mysql/

Answer 2

我的解决方案是在没有任何枢轴的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

Answer 3

对于MySQL，您可以将条件直接放入 sum（）函数中，并且将被评估为布尔值 0 或 1 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

Answer 4

对于动态枢轴，请使用 group_concat 与 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;

Answer 5

A 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;

db＆lt;＆gt; fiddle
_old sqlfiddle

如何？

true或null 产生 true 。
false或null 产生 null 。
null或null 产生 null 。
and cont a>仅计算非零值。瞧。

Answer 6

正确答案是：

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

Answer 7

有一个称为MySQL Pivot Table Generator的工具，它可以帮助您创建一个基于Web的枢轴表，稍后您可以将其导出到Excel（如果愿意）。如果您的数据位于单个表格或几个表中，则可以工作。

您需要做的就是指定列的数据源（它支持动态列），行，表主体中的值以及表关系（如果有）

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

Answer 8

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 ;

Answer 9

一个选项是组合 案例的使用。与 一起动态以及以下代码块中的结果集获得正确的列标题 ：

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

Answer 10

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;