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有错误也请指出，谢谢。
??? 全文完。
?
?
??
?
?
?
?
?
???
?
熱AI工具
Undresser.AI Undress
人工智慧驅動的應用程序，用於創建逼真的裸體照片
AI Clothes Remover
用於從照片中去除衣服的線上人工智慧工具。
Undress AI Tool
免費脫衣圖片
Clothoff.io
AI脫衣器
Video Face Swap
使用我們完全免費的人工智慧換臉工具，輕鬆在任何影片中換臉！
熱門文章
熱工具
記事本++7.3.1
好用且免費的程式碼編輯器
SublimeText3漢化版
中文版，非常好用
禪工作室 13.0.1
強大的PHP整合開發環境
Dreamweaver CS6
視覺化網頁開發工具
SublimeText3 Mac版
神級程式碼編輯軟體(SublimeText3)
MySQL和PhpMyAdmin：核心功能和功能
Apr 22, 2025 am 12:12 AM
MySQL和phpMyAdmin是強大的數據庫管理工具。 1）MySQL用於創建數據庫和表、執行DML和SQL查詢。 2）phpMyAdmin提供直觀界面進行數據庫管理、表結構管理、數據操作和用戶權限管理。
甲骨文在商業世界中的作用
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幫助企業優化運營。
MongoDB vs. Oracle：為您的需求選擇正確的數據庫
Apr 22, 2025 am 12:10 AM
MongoDB適合非結構化數據和高擴展性需求，Oracle適合需要嚴格數據一致性的場景。 1.MongoDB靈活存儲不同結構數據，適合社交媒體和物聯網。 2.Oracle結構化數據模型確保數據完整性，適用於金融交易。 3.MongoDB通過分片橫向擴展，Oracle通過RAC縱向擴展。 4.MongoDB維護成本低，Oracle維護成本高但支持完善。
在MySQL中解釋外鍵的目的。
Apr 25, 2025 am 12:17 AM
在MySQL中，外鍵的作用是建立表與表之間的關係，確保數據的一致性和完整性。外鍵通過引用完整性檢查和級聯操作維護數據的有效性，使用時需注意性能優化和避免常見錯誤。
比較和對比Mysql和Mariadb。
Apr 26, 2025 am 12:08 AM
MySQL和MariaDB的主要區別在於性能、功能和許可證：1.MySQL由Oracle開發，MariaDB是其分支。 2.MariaDB在高負載環境中性能可能更好。 3.MariaDB提供了更多的存儲引擎和功能。 4.MySQL採用雙重許可證，MariaDB完全開源。選擇時應考慮現有基礎設施、性能需求、功能需求和許可證成本。
SQL與MySQL：澄清兩者之間的關係
Apr 24, 2025 am 12:02 AM
SQL是一種用於管理關係數據庫的標準語言，而MySQL是一個使用SQL的數據庫管理系統。 SQL定義了與數據庫交互的方式，包括CRUD操作，而MySQL實現了SQL標準並提供了額外的功能，如存儲過程和触發器。
MySQL：數據庫，PHPMYADMIN：管理接口
Apr 29, 2025 am 12:44 AM
MySQL和phpMyAdmin可以通過以下步驟進行有效管理：1.創建和刪除數據庫：在phpMyAdmin中點擊幾下即可完成。 2.管理表：可以創建表、修改結構、添加索引。 3.數據操作：支持插入、更新、刪除數據和執行SQL查詢。 4.導入導出數據：支持SQL、CSV、XML等格式。 5.優化和監控：使用OPTIMIZETABLE命令優化表，並利用查詢分析器和監控工具解決性能問題。
如何安全地將包含函數和正則表達式的JavaScript對象存儲到數據庫並恢復？
Apr 19, 2025 pm 11:09 PM
安全地處理JSON中的函數和正則表達式在前端開發中，經常需要將JavaScript...
