MySQL rollup 函数?

发布网友发布时间：2022-04-30 18:54

我来回答

共2个回答

懂视网时间：2022-04-30 23:15

原文 http://53873039oycg.iteye.com/blog/2021445 主题 MySQL Oracle数据库

昨天突然在一篇博客中看到了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有错误也请指出，谢谢。

Mysql,Oracle使用rollup函数完成行列统计

标签：

热心网友时间：2022-04-30 20:23

oracle 中 rollup(A,(B,C