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

article/2025/9/13 14:45:42

时间 2014-02-25 00:05:38  ITeye-博客

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

本博文主要是记录下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);

要完成的效果如下：

5d997dff9e771d743c66e47b253efbe3.png

最简单的是使用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

结果是：

99dba9204d148f701168f88bbc341b60.png 

相当于：

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

结果是：

b3db29daa99e5f9f382bf8b613085604.png 

可以看出group by c1,c2 with rollup相当于group by c1,c2 union group by c1(c2替换为NULL) union (c1,c2全部替换为NULL)。

这里的替换规则参考了链接

原文是替换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中怎么写，想要的效果如图：

88bf7b33bbb29b4284ef5f57b2492cdf.png

首先建表。

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)

效果是：

32e8aad2ad5e104e91c56d5758f7db0c.png 

普通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有错误也请指出，谢谢。

时间： 11-20


http://chatgpt.dhexx.cn/article/aMHPqVc5.shtml

相关文章

Oracle-rollup()函数[转载]

Oracle-rollup()函数[转载]

参考学习了： http://blog.itpub.net/519536/viewspace-610995 http://blog.csdn.net/huang_xw/article/details/6402396 rollup()是group by的一个扩展函数，初步的感觉是，是gropu的升级版，可以多个列进行group by，然后…
阅读更多...
数据库小计和总计之 Rollup函数 简单介绍

数据库小计和总计之 Rollup函数 简单介绍

以下内容仅是站长或网友个人学习笔记、总结和研究收藏。不保证正确性 来源于 易百教程 SQL ROLLUP简介ROLLUP是GROUP BY子句的扩展。 ROLLUP选项允许包含表示小计的额外行，通常称为超级聚合行，以及总计行。 通过使用ROLLUP选项，可以使用单个…
阅读更多...
Oracle中rollup函数详解

Oracle中rollup函数详解

【基本介绍】 【格式】：group by rollup(字段1,字段2,字段3,...,字段n) 【说明】：rollup主要用于分组汇总，如果rollup中有n个字段，则会分别按【字段1】、【字段1,字段2】，【字段1,字段2,字段3】，...&…
阅读更多...
超硬核，SQL注入之时间盲注，原理+步骤+实战思路

超硬核，SQL注入之时间盲注，原理+步骤+实战思路

「作者主页」：士别三日wyx 「作者简介」：CSDN top100、阿里云博客专家、华为云享专家、网络安全领域优质创作者 「专栏简介」：此文章已录入专栏《网络安全快速入门》 时间盲注 一、什么是时间盲注？二、使用场景三、使用步骤第一步…
阅读更多...
SQL盲注注入——布尔型

SQL盲注注入——布尔型

盲注是注入的一种，指的是在不知道数据库返回值的情况下对数据中的内容进行猜测，实施SQL注入。盲注一般分为布尔盲注和基于时间的盲注和报错的盲注。本次主要讲解的是基于布尔的盲注。 Length（）函数 返回字符串的长度 Substr&…
阅读更多...
【SQL注入漏洞-04】布尔盲注靶场实战

【SQL注入漏洞-04】布尔盲注靶场实战

布尔盲注 当我们改变前端页面传输给后台sql参数时，页面没有显示相应内容也没有显示报错信息时，不能使用联合查询注入和报错注入，这时我们可以考虑是否为基于布尔的盲注。 布尔盲注原理： 利用页面返回的布尔类型状态，正…
阅读更多...
SQL注入之盲注简单总结

SQL注入之盲注简单总结

Mysql盲注总结 什么是盲注？ 盲注就是在sql注入过程中，sql语句执行的选择后，选择的数据不能回显到前端页面。此时，我们需要利用一些方法进行判断或者尝试，这个过程称之为盲注。SQL盲注与SQL普通注入的区别？…
阅读更多...
sql盲注

sql盲注

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档 sql注入--盲注 前言一、什么是盲注二、步骤示例1.测试注入点2.测所在数据库长度3. 利用ASCII码猜当前数据库名 4.利用ASCII码查询表名5、利用ASCII码查询字段名sql手注所用到…
阅读更多...
Sql盲注与普通注入的区别

Sql盲注与普通注入的区别

Sql盲注与普通注入的区别 在学习sql注入的时候，好多同学都是不弄清楚原理，去浏览器上狂搜一下注入语句，就开始对老师给的靶机注入，虽然能注入成功，但是不清楚原理，对以后的学习和工作没有多大的好处。现在我…
阅读更多...
SQL 注入-盲注

SQL 注入-盲注

目录 一、什么时候用到盲注？ 二、盲注的优缺点 三、盲注的流程 四、盲注的分类 五、布尔盲注 5.1、原理以及什么时候使用布尔盲注？ 5.2、例如upload-labs靶场中的Less-8 5.3、布尔盲注常用函数及定义 5.4、length()函数的使用方法 5.5、substr…
阅读更多...
Sqlmap使用-盲注小实验

Sqlmap使用-盲注小实验

目录 sqlmap简介 基本格式 sqlmap详细命令： Options（选项）： Target（目标）： Request（请求）： Enumeration（枚举）： O…
阅读更多...
SQL注入原理-布尔盲注

SQL注入原理-布尔盲注

小伙伴们大家好，今天为大家带来的使SQL注入原理之布尔盲注。 目录 布尔盲注使用的环境 常用函数与语句 substr()函数 ord()函数 length()函数 实战演示 1、判断是否存在注入点 2、尝试用报错盲注看是否能够成功爆出数据 3、使用布尔盲注来爆出数据信息…
阅读更多...
DVWA之sql注入——盲注

DVWA之sql注入——盲注

1.盲注 1.1 布尔盲注 布尔很明显的Ture跟Fales，也就说它只会根据你的注入信息返回Ture跟Fales，也就没有了之前的报错 信息。 1.判断是否存在注入，注入的类型 不管输入框输入为何内容，页面上只会返回以下2种情形的提示&#xff…
阅读更多...
SQL注入2——盲注（重学）

SQL注入2——盲注（重学）

SQL注入——盲注 SQL注入——盲注 SQL注入——盲注一、布尔型盲注1、判断2、盲注库名3、盲注表名4、盲注列名5、盲注数据 时间型盲注总结 一、布尔型盲注 假如网站一定存在SQL注入，当注入SQL语句（1’ and 12#）之后，网站页面缺没有…
阅读更多...
注入利用——盲注

注入利用——盲注

盲注基本概念： 盲注是注入的一种,指的是在不知道数据库返回值的情况下对数据中的内容进行猜测,实施SQL注入。 基于布尔的盲注 Web的页面的仅仅会返回True和False。那么布尔盲注就是进行SQL注入之后然后根据页面返回的True或者是False来得到数据库 中的相关信息。 …
阅读更多...
SQL注入-布尔盲注

SQL注入-布尔盲注

页面没有显示位 , 没有报错信息 , 只有成功和不成功两种情况时 , 可以使用布尔盲注 本次以 SQLi LABS 第9关为案例进行讲解 布尔盲注常用的两个函数(我觉得) length(abc) -- 判断字符串长度 substr(abc,1,1) -- 截取字符串,从第1个字符开始截取,截取1个 第一步,判断注入类型…
阅读更多...
SQL注入原理-时间盲注

SQL注入原理-时间盲注

小伙伴们大家好！本期为大家带来的是SQL注入原理之时间盲注。 目录 使用环境 常见函数与语句 sleep()函数 if语句 substr()函数 ord()函数 length()函数 实战演示 1、判断是否存在注入点 2、使用时间盲注爆出数据 1、爆出当前数据库名的长度 2、爆出数…
阅读更多...
sql注入之盲注相关知识

sql注入之盲注相关知识

盲注 一.概念理解： 盲注(Blind SQL Injection ),就是在 sql 注入过程中, sql 语句执行之后, 数据不能回显到前端页面，需要利用一些方法进行判断或者尝试。 二.盲注基本条件： 自己可以控制输入 原程序要执行的代码拼接了用户输入的数据。 …
阅读更多...
盲注--理论知识

盲注--理论知识

盲注的简单定义： 我们在查询过程中，页面只有两种情况：“true”和“false”，要么有，要么没有；查询表的记录和语句错误也不会显示。这种状况下的注入，称为盲注。 盲注的关键点： 根据…
阅读更多...
布尔盲注怎么用，一看你就明白了。布尔盲注原理+步骤+实战教程

布尔盲注怎么用，一看你就明白了。布尔盲注原理+步骤+实战教程

「作者主页」：士别三日wyx 「作者简介」：CSDN top100、阿里云博客专家、华为云享专家、网络安全领域优质创作者 「专栏简介」：此文章已录入专栏《网络安全快速入门》 布尔盲注 一、适用环境二、盲注步骤三、原理分析1. 长度判断原理2. 穷举字…
阅读更多...
推荐文章

Copyright @ 2022~2023