首页 文章

MySQL数据透视表

提问于
浏览 次
247

如果我有一个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 可以变化，因此输出列数量应该反映出来，每个 action / _ pagecount 对一列，然后每个 company_name 点击次数 . 我不确定这是否被称为数据透视表，但有人建议？

mysql pivot

8 回答

  • 2

    对于动态数据透视，请使用 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;

    DEMO HERE

    回复于
  • 27

    有一个名为MySQL Pivot表生成器的工具，它可以帮助您创建基于Web的数据透视表，以后可以导出到excel（如果您愿意） . 如果您的数据位于单个表或多个表中，它可以工作 .

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

    该工具的主页是http://mysqlpivottable.net

    回复于
  • 9
    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 ;
    回复于
  • 66

    正确的答案是：

    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
    回复于
  • 54

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

    有关如何实现这一目标的精彩教程可以在这里找到：http://www.artfulsoftware.com/infotree/qrytip.php?id=78

    我建议阅读这篇文章，并根据您的需求调整此解决方案 .

    Update

    在上面的链接目前不再可用之后，我觉得有必要为在这里搜索mysql数据库答案的所有人提供一些额外的信息 . 它真的有大量的信息，我不会把这里的所有东西放在这里（更多因为我只是不想复制他们丰富的知识），但我会就如何处理枢轴给出一些建议以sql的方式表示一般来自peku的例子，他们首先提出问题 .

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

    The spreadsheet way...

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

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

    The SQL way...

    鉴于他的表看起来像这样：

    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 .

    为了设置条件，这相当大声地使用CASE -statement . 为了分组，好吧，使用... GROUP BY .

    提供此pivot的基本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语句中定义的条件就越多 .

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

    关于此主题的一些其他链接：

    回复于
  • 197

    我的解决方案是在没有任何支点的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
    回复于
  • 6

    使用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.

    怎么样？

    TRUE OR NULL 收益 TRUE .
    FALSE OR NULL 收益 NULL .
    NULL OR NULL 收益 NULL .
    并且COUNT仅计算非空值 . 瞧 .

    回复于
  • 18

    对于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

    回复于

相关问题