社区
学习
工具库
AI工具
休闲
搜索
简体中文
首页 数据库 mysql教程 Mysql,Oracle使用rollup函数完成队列统计

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

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
Jun 07, 2016 pm 04:24 PM
mysql oracle rollup 使用 函数 完成

Mysql,Oracle使用rollup函数完成行列统计 ??? 昨天突然在 一篇博客中看到了Mysql也有rollup函数，原博文使用了rollup进行行列统计，原博文链接如下： ??? http://www.cnblogs.com/lhj588/archive/2012/06/15/2550392.html ??? 本博文主要是记录下mysql和oracl

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

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

??? 全文完。

?

?

??

?

?

?

?

?

???

?

本站声明
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn

热AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智能驱动的应用程序，用于创建逼真的裸体照片

AI Clothes Remover

AI Clothes Remover

用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool

Undress AI Tool

免费脱衣服图片

Clothoff.io

Clothoff.io

AI脱衣机

Video Face Swap

Video Face Swap

使用我们完全免费的人工智能换脸工具轻松在任何视频中换脸！

显示更多

热门文章

如何修复KB5055523无法在Windows 11中安装？
3 周前 By DDD
如何修复KB5055518无法在Windows 10中安装？
3 周前 By DDD
<🎜>：死铁路 - 如何驯服狼
1 个月前 By DDD
R.E.P.O.的每个敌人和怪物的力量水平
1 个月前 By 尊渡假赌尊渡假赌尊渡假赌
<🎜>：种植花园 - 完整的突变指南
2 周前 By DDD
显示更多

热工具

记事本++7.3.1

记事本++7.3.1

好用且免费的代码编辑器

SublimeText3汉化版

SublimeText3汉化版

中文版，非常好用

禅工作室 13.0.1

禅工作室 13.0.1

功能强大的PHP集成开发环境

Dreamweaver CS6

Dreamweaver CS6

视觉化网页开发工具

SublimeText3 Mac版

SublimeText3 Mac版

神级代码编辑软件(SublimeText3)

显示更多

热门话题

Java教程
1662
14
CakePHP 教程
1418
52
Laravel 教程
1311
25
PHP教程
1261
29
C# 教程
1234
24
显示更多
Related knowledge
MySQL的角色：Web应用程序中的数据库 MySQL的角色：Web应用程序中的数据库 Apr 17, 2025 am 12:23 AM

MySQL在Web应用中的主要作用是存储和管理数据。1.MySQL高效处理用户信息、产品目录和交易记录等数据。2.通过SQL查询，开发者能从数据库提取信息生成动态内容。3.MySQL基于客户端-服务器模型工作，确保查询速度可接受。

laravel入门实例 laravel入门实例 Apr 18, 2025 pm 12:45 PM

Laravel 是一款 PHP 框架，用于轻松构建 Web 应用程序。它提供一系列强大的功能，包括：安装： 使用 Composer 全局安装 Laravel CLI，并在项目目录中创建应用程序。路由： 在 routes/web.php 中定义 URL 和处理函数之间的关系。视图： 在 resources/views 中创建视图以呈现应用程序的界面。数据库集成： 提供与 MySQL 等数据库的开箱即用集成，并使用迁移来创建和修改表。模型和控制器： 模型表示数据库实体，控制器处理 HTTP 请求。

MySQL和PhpMyAdmin：核心功能和功能 MySQL和PhpMyAdmin：核心功能和功能 Apr 22, 2025 am 12:12 AM

MySQL和phpMyAdmin是强大的数据库管理工具。1）MySQL用于创建数据库和表、执行DML和SQL查询。2）phpMyAdmin提供直观界面进行数据库管理、表结构管理、数据操作和用户权限管理。

解决数据库连接问题：使用minii/db库的实际案例 解决数据库连接问题：使用minii/db库的实际案例 Apr 18, 2025 am 07:09 AM

在开发一个小型应用时，我遇到了一个棘手的问题：需要快速集成一个轻量级的数据库操作库。尝试了多个库后，我发现它们要么功能过多，要么兼容性不佳。最终，我找到了minii/db，这是一个基于Yii2的简化版本，完美地解决了我的问题。

MySQL与其他编程语言：一种比较 MySQL与其他编程语言：一种比较 Apr 19, 2025 am 12:22 AM

MySQL与其他编程语言相比，主要用于存储和管理数据，而其他语言如Python、Java、C 则用于逻辑处理和应用开发。 MySQL以其高性能、可扩展性和跨平台支持着称，适合数据管理需求，而其他语言在各自领域如数据分析、企业应用和系统编程中各有优势。

laravel框架安装方法 laravel框架安装方法 Apr 18, 2025 pm 12:54 PM

文章摘要：本文提供了详细分步说明，指导读者如何轻松安装 Laravel 框架。Laravel 是一个功能强大的 PHP 框架，它 упростил 和加快了 web 应用程序的开发过程。本教程涵盖了从系统要求到配置数据库和设置路由等各个方面的安装过程。通过遵循这些步骤，读者可以快速高效地为他们的 Laravel 项目打下坚实的基础。

甲骨文在商业世界中的作用 甲骨文在商业世界中的作用 Apr 23, 2025 am 12:01 AM

Oracle不仅是数据库公司，还是云计算和ERP系统的领导者。1.Oracle提供从数据库到云服务和ERP系统的全面解决方案。2.OracleCloud挑战AWS和Azure，提供IaaS、PaaS和SaaS服务。3.Oracle的ERP系统如E-BusinessSuite和FusionApplications帮助企业优化运营。

初学者的MySQL：开始数据库管理 初学者的MySQL：开始数据库管理 Apr 18, 2025 am 12:10 AM

MySQL的基本操作包括创建数据库、表格，及使用SQL进行数据的CRUD操作。1.创建数据库：CREATEDATABASEmy_first_db;2.创建表格：CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY,titleVARCHAR(100)NOTNULL,authorVARCHAR(100)NOTNULL,published_yearINT);3.插入数据：INSERTINTObooks(title,author,published_year)VA

See all articles