ROLLUP 分组查询总计

ROLLUP，是GROUP BY子句的一种扩展，可以为每个分组返回小计记录以及为所有分组返回总计记录。

CUBE，也是GROUP BY子句的一种扩展，可以返回每一个列组合的小计记录，同时在末尾加上总计记录。

select * from a;
1 a 1 1
2 a 2 2
3 b 3 3
4 b 4 4

SELECT grade, ID, SUM(num)
FROM a
GROUP BY ROLLUP(grade, ID)
order by grade, ID;


SELECT grade, ID, SUM(num)
FROM a
GROUP BY ROLLUP(grade, ID)
order by grade, ID;


1 a 1 1
2 a 2 2
3 a 3
4 b 3 3
5 b 4 4
6 b 7
7 10

SELECT grade, ID, SUM(num)
FROM a
GROUP BY CUBE(grade, ID)
order by grade, ID;

1 a 1 1
2 a 2 2
3 a 3
4 b 3 3
5 b 4 4
6 b 7
7 1 1
8 2 2
9 3 3
10 4 4
11 10