ORACLE: OCA-047-question and experiment (8)-usage of cube and Rollup

It's cool to listen to music, write a blog, and get it on a blog! This explains the functions of cube and rollup in SQL statements (used with group );

Question:

Explanation of the answer:... let's look at the example. It's hard to describe it.

There are a lot of examples on the Internet, so I can find one. It's not an infringement.

create table test(sales varchar2(10),dest varchar2(10),revenue number);insert into test values('smith','hangzhou',1000);insert into test values('smith','wenzhou',2000);insert into test values('allen','wenzhou',3000);insert into test values('allen','wenzhou',4000);commit;SQL> select * from test; SALES      DEST          REVENUE---------- ---------- ----------smith      hangzhou         1000smith      wenzhou          2000allen      wenzhou          3000allen      wenzhou          4000

The above is the preparation, and then let's look at Example 1:

SQL> select sales, nvl (DEST, 'Total'), sum (revenue) from test group by cube (sales, DEST); Sales nvl (DEST, 'Total ') sum (revenue) ---------- -------------- ------------ total 10000 Wenzhou 9000 Hangzhou 1000 Allen total 7000 Allen Wenzhou 7000 Smith total 3000 Smith Wenzhou 2000 Smith Hangzhou 1000 8 rows selected

There are eight rows in total. In the first two columns, the first column has two values, and the second column has two values. There are eight types of values based on the combination (including the null column, these eight cases are:

[Empty], [empty Wenzhou], [empty Hangzhou], [Allen empty], [Allen Wenzhou], [Smith empty], [Smith Wenzhou], [Smith Hangzhou], and then group by sums the third column value based on these eight values. This is the meaning of cube;

For the rollup function, see the following example 2:

SQL> select sales, nvl (DEST, 'Total'), sum (revenue) from test group by rollup (sales, DEST); Sales nvl (DEST, 'Total ') sum (revenue) ---------- -------------- ------------ Allen Wenzhou 7000 Allen total 7000 Smith Wenzhou 2000 Smith Hangzhou 1000 Smith total 3000 total 10000 6 rows selected

 

We can see that there are only six rows. Similarly, we can know the corresponding six combinations, so we will not list them here. Therefore, rollup has fewer types than cube combinations, but less than [empty Wenzhou] and [empty Hangzhou]. rollup meets this requirement, remove the [null *] combination you don't want to see.