What are the ways to use ROLLUP and CUBE
This article mainly explains "what are the ways to use ROLLUP and CUBE". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "what are the ways to use ROLLUP and CUBE?"
1. RULLUP
The function of group by with rollup clause can be understood as: first, multiple groups are generated according to certain rules, and then statistical data are grouped according to various groups.
Therefore, the main way to understand the use of group by followed by a rollup clause is to understand how it produces multiple groups according to certain rules. In addition, the result set returned by the group by followed by the rollup clause can be understood as the union of the result set generated by each group without removing the duplicated data.
1 、 Group by A, B
A) the number of grouped species produced: 1
That is, group by An and B
B) returns the result set: that is, the result set of this grouping.
2. Group by rollup (A, B)
A) the number of grouped species produced: 3
The first kind: group by An and B
The second kind: group by A
The third kind: group by NULL
The meaning is: there is no grouping, that is, all the data to do a statistics.
B) return the result set: count the union of the result set for the above three groups without removing the duplicate data.
3. Group by rollup (A, B, C)
A) the number of grouped species produced: 4
The first kind: group by An and B, C
The second kind: group by An and B
The third kind: group by A
The fourth kind: group by NULL
B) return the result set: count the union of the result set for the above four groups without removing the duplicate data.
4. Group by A, rollup (A, B)
A) the number of grouped species produced: 3
The first kind: group by A Magi A Magi B is equivalent to group by A Magi B.
The second kind: group by An is equivalent to group by A
The third kind: group by A null is equivalent to group by A
B) return the result set: count the union of the result set for the above three groups without removing the duplicate data.
5. Group by C, rollup (A, B)
A) the number of grouped species produced: 3
The first kind: group by C, Magi A, B
The second kind: group by CMagia A.
The third kind: group by C _ null is equivalent to group by C.
B) return the result set: count the union of the result set for the above three groups without removing the duplicate data.
6. Group by rollup ((A, B))
A) the number of grouped species produced: 2
The first kind: group by An and B
The second kind: group by NULL
B) return the result set: count the union of the result set for the above two groups without removing the duplicate data.
7. Group by rollup (A, (BMagee C))
A) the number of grouped species produced: 3
The first kind: group by An and B, C
The second kind: group by A
The third kind: group by NULL
B) return the result set: count the union of the result set for the above three groups without removing the duplicate data.
II. CUBE
Group by with a cube clause produces more grouped statistics. There will be as many groups as there are combinations of columns after cube (note that combinations are independent of order).
Group by cube (A, B, C)
1. The number of grouped species produced: 8
The first kind: group by An and B, C
The second kind: group by An and B
The third kind: group by An and C
The fourth kind: group by BMague C
The fifth kind: group by C
The sixth kind: group by B
Seventh: group by A
Type 8: group by NULL
2. Return the result set: count the union of the result set for the above eight groups without removing the duplicate data.
At this point, I believe you have a deeper understanding of "what is the use of ROLLUP and CUBE?" you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!
