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
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