Mysql and Oracle use the rollup function to complete queue statistics

Mysql and Oracle use the rollup function to complete row and column statistics ??? Yesterday, I suddenly saw in a blog that Mysql also had the rollup function. The original blog post used rollup for row and column statistics. The original blog post link is as follows :??? Www.cnblogs.comlhj588archive201206152550392.html ??? This blog post mainly records mysql and CMDL

Mysql and Oracle use the rollup function to complete row and column statistics ??? Yesterday, I suddenly saw in a blog that Mysql also had the rollup function. The original blog post used rollup for row and column statistics. The original blog post link is as follows :??? Http://www.cnblogs.com/lhj588/archive/2012/06/15/2550392.html ??? This blog post mainly records mysql and CMDL

Mysql and Oracle use the rollup function to complete row and column statistics

??? Yesterday, I suddenly saw in a blog that Mysql also had the rollup function. The original blog post used rollup for row and column statistics. The original blog post link is as follows:

??? Http://www.cnblogs.com/lhj588/archive/2012/06/15/2550392.html

??? This blog post mainly records the rows and columns of mysql and oracle using the rollup function for statistics. The content is relatively simple.

??? The first is mysql, table creation test:

???

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);

?? The effect is as follows:

???

????? The simplest is to use union, as shown below:

?????

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 

??? You can also use the with rollup function. Note that when rollup is used, you cannot use the order by clause to sort the results at the same time.

???

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 is actually the first simplification.

?? You can also write as follows:

???

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;

??? The HAVING c1 is not null condition mainly filters out the row that sums the entire tmysql_test_hanglietongji table. The preceding subquery IS used as an example:

???

SELECT c1, IFNULL(c2, 'total') AS c2, SUM(c3) AS c3          FROM tmysql_test_hanglietongji          GROUP BY c1, c2 WITH ROLLUP

??? The result is:

???

?? Equivalent:

??

SELECT c1, IFNULL(c2, 'total') AS c2, SUM(c3) AS c3FROM tmysql_test_hanglietongji GROUP BY c1, c2union ALLSELECT c1, 'total' AS c2, SUM(c3) AS c3FROM tmysql_test_hanglietongji GROUP BY c1union ALLSELECT NULL, 'total' AS c2, SUM(c3) AS c3FROM tmysql_test_hanglietongji 

??? The result is:

???

??? We can see that group by c1, c2 with rollup is equivalent to group by c1, c2 union group by c1 (c2 is replaced with NULL) union? (Replace c1 and c2 with NULL ).

?? The replacement rules here refer to the link

?? Http://blog.itpub.net/519536/viewspace-610995

?? The original article is to replace Oracle's rollup, which is also applicable in Mysql.

?? The common SQL statement is as follows:

??

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 c1UNIONSELECT '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

?? The missing one is because the above having requires c1 is not null, so the replacement of c1 with NULL is gone.

?

?? The following describes how to write data in oracle and the expected results.

???

?? Create a table first.

??

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);

?? The simplest method is:

???

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 C1UNIONSELECT '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

?? Then use the rollup function to simplify the process.

???

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)

??? You can also write this statement:

??

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 and common SQL replace are also described above. For example:

??

SELECT c1, nvl(c2, 'total') AS c2, SUM(c3) AS c3          FROM tsql_test_hanglietongji         GROUP BY ROLLUP(c1, c2)

? Effect:

??

?? The common SQL statement is as follows:

??

SELECT c1, nvl(c2, 'total') AS c2, SUM(c3) AS c3  FROM tsql_test_hanglietongji GROUP BY c1, c2union allSELECT c1, nvl(null, 'total') AS c2, SUM(c3) AS c3  FROM tsql_test_hanglietongji GROUP BY c1union allSELECT NULL, 'total' AS c2, SUM(c3) AS c3  FROM tsql_test_hanglietongji order by 1, 2

??? Careful friends may have noticed that the second union all has order by 1 and 2, while the above mysql does not have order by, which is related to mysql and oracle's default sorting rules for NULL.

??? Rewrite rollup with a common SQL statement:

???

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 c1union allSELECT 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 is also excluded here.

??? Through the comparison above, we found that the rollup of oracle and mysql is very similar. If you are interested in the rollup function, Please carefully search for rollup to learn.

??? This is the end. Please leave a message if you have any comments. If there is an SQL error in the text, please point it out. Thank you.

??? The full text is complete.

?

?

??

?

?

?

?

?

???

?