ID FIRST_NAME LAST_NAME
---------- ---------- ----------
1 a b
2 a b
3 c d
4 e f
4 e f
SQL> select * from test group by id,cube(first_name,last_name); ---这个就是先按照id来分组,然后,在cube(first_name,last_name)
ID FIRST_NAME LAST_NAME
---------- ---------- ----------
1
1 b
1 a
1 a b
2
2 b
2 a
2 a b
3
3 d
3 c
ID FIRST_NAME LAST_NAME
---------- ---------- ----------
3 c d
4
4 f
4 e
4 e f
已选择16行。
SQL> select first_name,last_name from test group by cube(first_name,last_name);
FIRST_NAME LAST_NAME
---------- ----------
b
d
f
a
a b
c
c d
e
e f
已选择10行。
SQL> select * from test group by cube(id,first_name,last_name); group by id,first_name,group by id,last_name,group by id,first_name,last_name,group by first_name,last_name
ID FIRST_NAME LAST_NAME
---------- ---------- ----------
b
d
f
a
a b
c
c d
e
e f
1
ID FIRST_NAME LAST_NAME
---------- ---------- ----------
1 b
1 a
1 a b
2
2 b
2 a
2 a b
3
3 d
3 c
3 c d
ID FIRST_NAME LAST_NAME
---------- ---------- ----------
4
4 f
4 e
4 e f
已选择26行。
SQL>
SQL> select first_name,last_name from test group by cube(first_name,last_name) having grouping(last_name)=1;--找到由cube聚合导致last_name为空的记录
FIRST_NAME LAST_NAME
---------- ----------
a
c
e
SQL> select first_name,last_name from test group by cube(first_name,last_name) having grouping(last_name)=1 and grouping(first_name)=1;
FIRST_NAME LAST_NAME
---------- ----------
SQL> select count(*) from (select first_name,last_name from test group by cube(first_name,last_name) having grouping(last_name)=1 and grouping(first_name)=1)
COUNT(*)
----------
1
找到由于cube导致的first_name与last_name都为空的记录