昨天突然在一篇博客中看到了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有错误也请指出,谢谢。
全文完。
相关推荐
学习过 SQL 的人都知道,使用聚合函数(AVG、SUM、COUNT、MIN/MAX 等)和分组操作(GROUP BY)可以对数据进行基本的统计分析,例如统计公司员工的人数、每个部门的平均月薪等。如果想要回顾这些基础概念,可以参考这...
5. **SQL 功能扩展**:增加了窗口函数、GROUP BY with ROLLUP 和 CUBE 功能,以及用户定义变量的增强,提升了 SQL 编程的灵活性和功能性。 6. **复制改进**:改进了复制功能,包括GTID(全局事务标识符)的默认启用...
这些问题不仅适用于MySQL,也可以推广到其他类似的关系型数据库,如Oracle、DB2等。 ### 题目1 #### 问题1.1 为了查询第一季度各类型的记录数累计值,我们需要计算每个类型在2023年1月1日至3月31日之间的记录数量...
需要将聚合函数(如 min、max 等)用到的字段一起设置为联合索引。例如,在本实例中,需要将 device_id、product_id 和 log_time 三个字段设置为联合索引。 知识点2:索引字段的选择 在设置索引时,需要选择合适的...
数据库复习题2(答案)复习进程 数据库复习题2(答案)复习进程主要涵盖了数据库系统的基本概念和技术,包括...rollup 是一种聚合函数,用于计算分组统计结果。cube 是一种查询语言,用于生成所有可能的分组统计结果。
│ SQL Server到Oracle连接服务器的实现.sql │ SQL Server到SQLBASE连接服务器的实现.sql │ SQL Server到SYBASE连接服务器的实现.sql │ sql导出mysql.sql │ textcopy实现文件存取.sql │ Vb程序实现文件存取.sql...
11. 分区和聚合函数:PARTITION BY用于数据分区,GROUP BY和ROLLUP用于数据分组,COUNT、SUM、AVG、MAX和MIN是常用的聚合函数,用于统计和汇总数据。 12. 存储过程和触发器:存储过程是一组预编译的SQL语句,可以...
- 数据库:存储数据的结构化集合,如MySQL、Oracle、SQL Server等。 - 表:数据库中的基本单元,由列(字段)和行(记录)组成。 - 数据类型:包括数值类型(如INT、FLOAT)、字符串类型(如VARCHAR、CHAR)、...
它还拥有完善的权限管理能力和分析功能,包括CTE、窗口函数、层次查询、pivot以及groupingSets/rollup等。安全功能上,OceanBase提供权限控制、审计以及加密支持,且新功能和行为将根据客户和市场需求来逐步支持。 ...
│ 3.4.2 分段更新函数.sql │ │ 3.4.3 IP地址处理函数.sql │ │ 3.5.1 字符串比较函数.sql │ │ 3.5.2 字符串并集&交集处理示例.sql │ │ 3.5.3 字符串分拆并统计的处理示例.sql │ │ ...
此外,你还需要熟悉特定数据库系统的特性,如MySQL、Oracle、SQL Server或PostgreSQL的特有功能。每个系统可能在语法上略有差异,但基本概念是通用的。 通过参与"SQL挑战",你不仅可以提高SQL编程技能,还能锻炼...
<jp:mondrianQuery dataSource="" id="query01" jdbcDriver="oracle.jdbc.driver.OracleDriver" jdbcUrl="jdbc:oracle:thin:ngykt/ngyktadmin@172.16.46.241:1521:orcl10" catalogUri="/WEB-INF/queries/feeSchema....