昨天突然在一篇博客中看到了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 c1 IS NOT NULL) AS A GROUP BY c1 WITH ROLLUP;
HAVING c1 IS NOT NULL条件主要是过滤掉对整个tmysql_test_hanglietongji 表求和的那一行,以上面的子查询为例:
SELECT c1, IFNULL(c2, 'total') AS c2, SUM(c3) AS c3 FROM tmysql_test_hanglietongji GROUP BY c1, c2 WITH ROLLUP
结果是:
相当于:
SELECT c1, IFNULL(c2, 'total') AS c2, SUM(c3) AS c3 FROM tmysql_test_hanglietongji GROUP BY c1, c2 union ALL SELECT c1, 'total' AS c2, SUM(c3) AS c3 FROM tmysql_test_hanglietongji GROUP BY c1 union ALL SELECT NULL, 'total' AS c2, SUM(c3) AS c3 FROM tmysql_test_hanglietongji
结果是:
可以看出group by c1,c2 with rollup相当于group by c1,c2 union group by c1(c2替换为NULL) union (c1,c2全部替换为NULL)。
这里的替换规则参考了链接
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 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(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 C1 UNION SELECT '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函数简化。
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)
效果是:
普通sql写法是:
SELECT c1, nvl(c2, 'total') AS c2, SUM(c3) AS c3 FROM tsql_test_hanglietongji GROUP BY c1, c2 union all SELECT c1, nvl(null, 'total') AS c2, SUM(c3) AS c3 FROM tsql_test_hanglietongji GROUP BY c1 union all SELECT NULL, 'total' AS c2, SUM(c3) AS c3 FROM tsql_test_hanglietongji order by 1, 2
细心的朋友也许注意到了,第二个union all带了order by 1,2而上面的mysql没有带order by,这和mysql和oracle对NULL的默认排序规则有关。
使用普通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 c1 union all SELECT 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的情况。
通过上面的对比,发现oracle和mysql的rollup非常相似,对rollup函数感兴趣的朋友请仔细搜索rollup学习。
到这里该结束了,有任何意见请留言,如文中sql有错误也请指出,谢谢。
全文完。
相关推荐
本博客简单介绍一下oracle分组函数之rollup的用法,rollup函数常用于分组统计,也是属于oracle分析函数的一种 环境准备 create table dept as select * from scott.dept; create table emp as select * from ...
使用ROLLUP函数生成报表的小计、合计 这个函数很不错 使用的范围其实蛮广的
Oracle的Rollup用法 Oracle的Rollup用法
NULL 博文链接:https://dolphin-ygj.iteye.com/blog/416356
oracle 聚合函数的扩展使用 1.ROLLUP子句 ROLLUP是GROUP BY子句的扩展,它是为每一个分组返回一条合计记录,并为全部分组返回总计。 2.CUBE子句也是对GROUP BY子句进行扩展,返回CUBE中所有列组合的小计信息,...
group by 后 带 rollup 子句的功能可以理解为:先按一定的规则产生多种分组,然后按各种分组统计数据(至于统计出的数据是求和还是最大值还是平均值等这就取决于SELECT后的聚合函数)。因此要搞懂group by 后 带 ...
关于oracle的所有分析函数的使用,像rollup,cube之类的.
Oracle 分析函数详解 1. 自动汇总函数rollup,cube, 2. rank 函数, rank,dense_rank,row_number 3. lag,lead函数 4. sum,avg,的移动增加,移动平均数 5. ratio_to_report报表处理函数 6. first,last取基数的分析函数
·1Oracle分析函数一——函数列表 ...·5Oracle分析函数五——统计分析函数 ·6Oracle分析函数六——数据分布函数及报表函数 ·7Oracle分析函数七——分析函数案例 ·8Oracle分析函数八——CUBE,ROLLUP
group by后使用rollup子句总结,oracle开发人员值得一看
当行由 CUBE 或 ROLLUP 运算符添加时,该函数将导致附加列的输出值为 1;当行不由 CUBE 或 ROLLUP 运算符添加时,该函数将导致附加列的输出值为 0。 仅在与包含 CUBE 或 ROLLUP 运算符的 GROUP BY 子句相关联的选择...
深入介结分析函数的内部实现cube,rollup,grouping,里面有具体的例子来说明,方便大家理解学习
NULL 博文链接:https://tom-tomcat.iteye.com/blog/2041025
从论坛搜到的,感觉还...1. 自动汇总函数rollup,cube, 2. rank 函数, rank,dense_rank,row_number 3. lag,lead函数 4. sum,avg,的移动增加,移动平均数 5. ratio_to_report报表处理函数 6. first,last取基数的分析函数
NULL 博文链接:https://wangjingyi.iteye.com/blog/1545090
oracle rollup及cube的使用,开发人员必备的资料
About Oracle Applications Technology ATG_PF.H Rollup 6
NULL 博文链接:https://wankunde.iteye.com/blog/1879565
14.3 使用ROLLUP、GROUPING和CUBE 14.4 家族树和COlLrlectby 14.4.1 排除个体和分支 14.4.2 向根遍历 14.4.3 基本规则 第15章 更改数据:插入、更新、合并和删除 第16章 DECODE和CASE.SQL中的if-fhen-else 第17章 ...