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有错误也请指出,谢谢。
??? 全文完。
?
?
??
?
?
?
?
?
???
?

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics



MySQL's position in databases and programming is very important. It is an open source relational database management system that is widely used in various application scenarios. 1) MySQL provides efficient data storage, organization and retrieval functions, supporting Web, mobile and enterprise-level systems. 2) It uses a client-server architecture, supports multiple storage engines and index optimization. 3) Basic usages include creating tables and inserting data, and advanced usages involve multi-table JOINs and complex queries. 4) Frequently asked questions such as SQL syntax errors and performance issues can be debugged through the EXPLAIN command and slow query log. 5) Performance optimization methods include rational use of indexes, optimized query and use of caches. Best practices include using transactions and PreparedStatemen

Apache connects to a database requires the following steps: Install the database driver. Configure the web.xml file to create a connection pool. Create a JDBC data source and specify the connection settings. Use the JDBC API to access the database from Java code, including getting connections, creating statements, binding parameters, executing queries or updates, and processing results.

In Oracle, the FOR LOOP loop can create cursors dynamically. The steps are: 1. Define the cursor type; 2. Create the loop; 3. Create the cursor dynamically; 4. Execute the cursor; 5. Close the cursor. Example: A cursor can be created cycle-by-circuit to display the names and salaries of the top 10 employees.

To stop an Oracle database, perform the following steps: 1. Connect to the database; 2. Shutdown immediately; 3. Shutdown abort completely.

SQL statements can be created and executed based on runtime input by using Oracle's dynamic SQL. The steps include: preparing an empty string variable to store dynamically generated SQL statements. Use the EXECUTE IMMEDIATE or PREPARE statement to compile and execute dynamic SQL statements. Use bind variable to pass user input or other dynamic values to dynamic SQL. Use EXECUTE IMMEDIATE or EXECUTE to execute dynamic SQL statements.

The process of starting MySQL in Docker consists of the following steps: Pull the MySQL image to create and start the container, set the root user password, and map the port verification connection Create the database and the user grants all permissions to the database

Installing MySQL on CentOS involves the following steps: Adding the appropriate MySQL yum source. Execute the yum install mysql-server command to install the MySQL server. Use the mysql_secure_installation command to make security settings, such as setting the root user password. Customize the MySQL configuration file as needed. Tune MySQL parameters and optimize databases for performance.

Building a Hadoop Distributed File System (HDFS) on a CentOS system requires multiple steps. This article provides a brief configuration guide. 1. Prepare to install JDK in the early stage: Install JavaDevelopmentKit (JDK) on all nodes, and the version must be compatible with Hadoop. The installation package can be downloaded from the Oracle official website. Environment variable configuration: Edit /etc/profile file, set Java and Hadoop environment variables, so that the system can find the installation path of JDK and Hadoop. 2. Security configuration: SSH password-free login to generate SSH key: Use the ssh-keygen command on each node
