Oracle的几种group by

create table TEST as select * from DBA_INDEXES;
commit;

--普通group by
SELECT 
    OWNER, INDEX_TYPE, STATUS, COUNT(*) 
FROM TEST 
WHERE OWNER LIKE 'SY%' 
GROUP BY OWNER, INDEX_TYPE, STATUS;

/*group by rollup(A,B,C)
GROUP BY ROLLUP(A, B, C)：
首先会对(A、B、C)进行GROUP BY，
然后再对(A、B)进行GROUP BY，
其后再对(A)进行GROUP BY，
最后对全表进行汇总操作。*/
SELECT 
    OWNER, INDEX_TYPE, STATUS, COUNT(*) 
FROM TEST 
WHERE OWNER LIKE 'SY%' 
GROUP BY ROLLUP(OWNER, INDEX_TYPE, STATUS);

/*group by cube(A,B,C)
GROUP BY CUBE(A, B, C)：
则首先会对(A、B、C)进行GROUP BY，
然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，
最后对全表进行汇总操作。*/
SELECT 
    OWNER, INDEX_TYPE, STATUS, COUNT(*) 
FROM TEST 
WHERE OWNER LIKE 'SY%' 
GROUP BY CUBE(OWNER, INDEX_TYPE, STATUS);