昨天突然在 一篇博客中看到了Mysql也有rollup函數(shù)，原博文使用了rollup進(jìn)行行列統(tǒng)計，原博文鏈接如下：
http://www.cnblogs.com/lhj588/archive/2012/06/15/2550392.html
本博文主要是記錄下mysql和oracle使用rollup函數(shù)進(jìn)行行列統(tǒng)計，內(nèi)容比較簡單。
首先是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函數(shù)。注意當(dāng)使用 rollup時, 你不能同時使用 order by子句進(jìn)行結(jié)果排序
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 c1 IS NOT NULL) AS A GROUP BY c1 WITH ROLLUP;
HAVING c1 IS NOT NULL條件主要是過濾掉對整個tmysql_test_hanglietongji 表求和的那一行，以上面的子查詢?yōu)槔?/p>
SELECT c1, IFNULL(c2, 'total') AS c2, SUM(c3) AS c3 FROM tmysql_test_hanglietongji GROUP BY c1, c2 WITH ROLLUP
結(jié)果是：
SELECT c1, IFNULL(c2, 'total') AS c2, SUM(c3) AS c3FROM tmysql_test_hanglietongji GROUP BY c1, c2union ALLSELECT c1, 'total' AS c2, SUM(c3) AS c3FROM tmysql_test_hanglietongji GROUP BY c1union ALLSELECT NULL, 'total' AS c2, SUM(c3) AS c3FROM tmysql_test_hanglietongji
結(jié)果是：
這里的替換規(guī)則參考了鏈接
http://blog.itpub.net/519536/viewspace-610995
原文是替換Oracle的rollup，在Mysql中也適用。
使用普通sql寫法是：
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 HAVING c1 IS NOT NULL union SELECT c1, 'total' as c2, SUM(c3) AS c3 FROM tmysql_test_hanglietongji group by c1) A group by c1UNIONSELECT '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 HAVING c1 IS NOT NULL union SELECT c1, 'total' as c2, SUM(c3) AS c3 FROM tmysql_test_hanglietongji group by c1) A
少了一個是因為上面的having要求c1 is not null,所以替換c1為NULL就沒有了。
下面看下oracle中怎么寫，想要的效果如圖：
create table TSQL_TEST_HANGLIETONGJI( ID NUMBER(4) not null, C1 VARCHAR2(2), C2 VARCHAR2(2), C3 NUMBER(4));alter table TSQL_TEST_HANGLIETONGJI add primary key (ID);insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3)values (1, 'A1', 'B1', 9);insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3)values (2, 'A2', 'B1', 7);insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3)values (3, 'A3', 'B1', 4);insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3)values (4, 'A4', 'B1', 2);insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3)values (5, 'A1', 'B2', 2);insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3)values (6, 'A2', 'B2', 9);insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3)values (7, 'A3', 'B2', 8);insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3)values (8, 'A4', 'B2', 5);insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3)values (9, 'A1', 'B3', 1);insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3)values (10, 'A2', 'B3', 8);insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3)values (11, 'A3', 'B3', 8);insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3)values (12, 'A4', 'B3', 6);insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3)values (13, 'A1', 'B4', 8);insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3)values (14, 'A2', 'B4', 2);insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3)values (15, 'A3', 'B4', 6);insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3)values (16, 'A4', 'B4', 9);insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3)values (17, 'A1', 'B4', 3);insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3)values (18, 'A2', 'B4', 5);insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3)values (19, 'A3', 'B4', 2);insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3)values (20, 'A4', 'B4', 5);
最簡單的寫法是：
select c1, sum(decode(c2,'B1', C3, 0)) AS B1, sum(decode(c2 ,'B2', C3, 0)) AS B2, sum(decode(c2 ,'B3', C3, 0)) AS B3, sum(decode(c2 ,'B4', C3, 0)) AS B4, SUM(C3) AS TOTAL from tsql_test_hanglietongji group by C1UNIONSELECT 'TOTAL', sum(decode(c2 ,'B1', C3, 0)) AS B1, sum(decode(c2 ,'B2', C3, 0)) AS B2, sum(decode(c2 ,'B3', C3, 0)) AS B3, sum(decode(c2 ,'B4', C3, 0)) AS B4, SUM(C3) FROM tsql_test_hanglietongji
然后使用rollup函數(shù)簡化。
SELECT nvl(c1, 'total') AS total, SUM(decode(c2, 'B1', c3, 0)) AS B1, SUM(decode(c2, 'B2', c3, 0)) AS B2, SUM(decode(c2, 'B3', c3, 0)) AS B3, SUM(decode(c2, 'B4', c3, 0)) AS B4, sum(c3) AS total FROM tsql_test_hanglietongji GROUP BY ROLLUP(c1)
也可以這么寫：
SELECT nvl(c1, 'total') AS total_c, SUM(decode(c2, 'B1', c3, 0)) AS B1, SUM(decode(c2, 'B2', c3, 0)) AS B2, SUM(decode(c2, 'B3', c3, 0)) AS B3, SUM(decode(c2, 'B4', c3, 0)) AS B4, SUM(decode(c2, 'total', c3, 0)) AS total_r FROM (SELECT c1, nvl(c2, 'total') AS c2, SUM(c3) AS c3 FROM tsql_test_hanglietongji GROUP BY ROLLUP(c1, c2) HAVING c1 IS NOT NULL) A GROUP BY ROLLUP(c1);
rollup和普通sql替換上面也說了，舉個例子：
SELECT c1, nvl(c2, 'total') AS c2, SUM(c3) AS c3 FROM tsql_test_hanglietongji GROUP BY ROLLUP(c1, c2)
效果是：
SELECT c1, nvl(c2, 'total') AS c2, SUM(c3) AS c3 FROM tsql_test_hanglietongji GROUP BY c1, c2union allSELECT c1, nvl(null, 'total') AS c2, SUM(c3) AS c3 FROM tsql_test_hanglietongji GROUP BY c1union allSELECT NULL, 'total' AS c2, SUM(c3) AS c3 FROM tsql_test_hanglietongji order by 1, 2
細(xì)心的朋友也許注意到了，第二個union all帶了order by 1,2而上面的mysql沒有帶order by,這和mysql和oracle對NULL的默認(rèn)排序規(guī)則有關(guān)。
使用普通sql重寫rollup為：
SELECT nvl(c1, 'total') AS total_c, SUM(decode(c2, 'B1', c3, 0)) AS B1, SUM(decode(c2, 'B2', c3, 0)) AS B2, SUM(decode(c2, 'B3', c3, 0)) AS B3, SUM(decode(c2, 'B4', c3, 0)) AS B4, SUM(decode(c2, 'total', c3, 0)) AS total_r FROM (SELECT c1, nvl(c2, 'total') AS c2, SUM(c3) AS c3 FROM tsql_test_hanglietongji GROUP BY c1, c2 HAVING c1 IS NOT NULL union all SELECT c1, nvl(null, 'total') AS c2, SUM(c3) AS c3 FROM tsql_test_hanglietongji GROUP BY c1 HAVING c1 IS NOT NULL) A GROUP BY c1union allSELECT nvl(null, 'total') AS total_c, SUM(decode(c2, 'B1', c3, 0)) AS B1, SUM(decode(c2, 'B2', c3, 0)) AS B2, SUM(decode(c2, 'B3', c3, 0)) AS B3, SUM(decode(c2, 'B4', c3, 0)) AS B4, SUM(decode(c2, 'total', c3, 0)) AS total_r FROM (SELECT c1, nvl(c2, 'total') AS c2, SUM(c3) AS c3 FROM tsql_test_hanglietongji GROUP BY c1, c2 HAVING c1 IS NOT NULL union all SELECT c1, nvl(null, 'total') AS c2, SUM(c3) AS c3 FROM tsql_test_hanglietongji GROUP BY c1 HAVING c1 IS NOT NULL) A order by 1
這里也排除了c1 is null的情況。
通過上面的對比，發(fā)現(xiàn)oracle和mysql的rollup非常相似，對rollup函數(shù)感興趣的朋友請仔細(xì)搜索rollup學(xué)習(xí)。
到這里該結(jié)束了，有任何意見請留言，如文中sql有錯誤也請指出，謝謝。
