??? 昨天突然在一篇博客中看到了Mysql也有rollup函数，原博文使用了rollup进行行列统计，原博文链接如下：

??? http://www.cnblogs.com/lhj588/archive/2012/06/15/2550392.html

??? 本博文主要是记录下mysql和oracle使用rollup函数进行行列统计，内容比较简单。

??? 首先是mysql，建表测试：

???

CREATE TABLE `tmysql_test_hanglietongji` (
  `id` int(11) NOT NULL,
  `c1` char(2) COLLATE utf8_bin DEFAULT NULL,
  `c2` char(2) COLLATE utf8_bin DEFAULT NULL,
  `c3` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

???

INSERT INTO `tmysql_test_hanglietongji` VALUES (1, 'A1', 'B1', 9);
INSERT INTO `tmysql_test_hanglietongji` VALUES (2, 'A2', 'B1', 7);
INSERT INTO `tmysql_test_hanglietongji` VALUES (3, 'A3', 'B1', 4);
INSERT INTO `tmysql_test_hanglietongji` VALUES (4, 'A4', 'B1', 2);
INSERT INTO `tmysql_test_hanglietongji` VALUES (5, 'A1', 'B2', 2);
INSERT INTO `tmysql_test_hanglietongji` VALUES (6, 'A2', 'B2', 9);
INSERT INTO `tmysql_test_hanglietongji` VALUES (7, 'A3', 'B2', 8);
INSERT INTO `tmysql_test_hanglietongji` VALUES (8, 'A4', 'B2', 5);
INSERT INTO `tmysql_test_hanglietongji` VALUES (9, 'A1', 'B3', 1);
INSERT INTO `tmysql_test_hanglietongji` VALUES (10, 'A2', 'B3', 8);
INSERT INTO `tmysql_test_hanglietongji` VALUES (11, 'A3', 'B3', 8);
INSERT INTO `tmysql_test_hanglietongji` VALUES (12, 'A4', 'B3', 6);
INSERT INTO `tmysql_test_hanglietongji` VALUES (13, 'A1', 'B4', 8);
INSERT INTO `tmysql_test_hanglietongji` VALUES (14, 'A2', 'B4', 2);
INSERT INTO `tmysql_test_hanglietongji` VALUES (15, 'A3', 'B4', 6);
INSERT INTO `tmysql_test_hanglietongji` VALUES (16, 'A4', 'B4', 9);
INSERT INTO `tmysql_test_hanglietongji` VALUES (17, 'A1', 'B4', 3);
INSERT INTO `tmysql_test_hanglietongji` VALUES (18, 'A2', 'B4', 5);
INSERT INTO `tmysql_test_hanglietongji` VALUES (19, 'A3', 'B4', 2);
INSERT INTO `tmysql_test_hanglietongji` VALUES (20, 'A4', 'B4', 5);

?? 要完成的效果如下：

???

????? 最简单的是使用union,如下：

?????

select ifnull(c1, 'total') as 'total',
       sum(if(c2 = 'B1', C3, 0)) AS B1,
       sum(if(c2 = 'B2', C3, 0)) AS B2,
       sum(if(c2 = 'B3', C3, 0)) AS B3,
       sum(if(c2 = 'B4', C3, 0)) AS B4,
       SUM(C3) AS TOTAL
  from tmysql_test_hanglietongji
 group by C1 
union 
select 'total' as 'total',
       sum(if(c2 = 'B1', C3, 0)) AS B1,
       sum(if(c2 = 'B2', C3, 0)) AS B2,
       sum(if(c2 = 'B3', C3, 0)) AS B3,
       sum(if(c2 = 'B4', C3, 0)) AS B4,
       SUM(C3) AS TOTAL
  from tmysql_test_hanglietongji
 order by 1 

??? 也可以使用with rollup函数。注意当使用 rollup时, 你不能同时使用 order by子句进行结果排序

???

select ifnull(c1, 'total') 'total',
       sum(if(c2 = 'B1', C3, 0)) AS B1,
       sum(if(c2 = 'B2', C3, 0)) AS B2,
       sum(if(c2 = 'B3', C3, 0)) AS B3,
       sum(if(c2 = 'B4', C3, 0)) AS B4,
       SUM(C3) AS TOTAL
  from tmysql_test_hanglietongji
 group by C1 with rollup;

?? with rollup其实是第一个的简化。

?? 也可以这样写：

???

SELECT IFNULL(c1, 'total') AS total,
       SUM(IF(c2 = 'B1', c3, 0)) AS B1,
       SUM(IF(c2 = 'B2', c3, 0)) AS B2,
       SUM(IF(c2 = 'B3', c3, 0)) AS B3,
       SUM(IF(c2 = 'B4', c3, 0)) AS B4,
       SUM(IF(c2 = 'total', c3, 0)) AS total
  FROM (SELECT c1, IFNULL(c2, 'total') AS c2, SUM(c3) AS c3
          FROM tmysql_test_hanglietongji 
         GROUP BY c1, c2 WITH ROLLUP
        HAVING