mysql中需要注意两点：
（1）rollup和order by互斥
root@localhost:gw1 04:23:17> select year(orderdate) as year,sum(qty) as sum from t group by year(orderdate) with rollup order by year;
ERROR 1221 (HY000): Incorrect usage of CUBE/ROLLUP and ORDER BY
（2）如果分组的列包含null值，那么rollup的结果可能是不正确的，因为在rollup中进行分组统计时值null具有特殊的意义。
（3）mysql（5.6）只支持rollup，不支持cube
下面先看mysql的rollup操作：
select year(orderdate) as year,sum(qty) as sum from t group by year(orderdate) with rollup;
+------+------+
| year | sum |
+------+------+
| 2009 | 30 |
| 2010 | 100 |
| 2011 | 90 |
| NULL | 220 |
+------+------+
4 rows in set (0.00 sec)
select empid,custid,year(orderdate) year,sum(qty) sum
from t
group by empid,custid,year(orderdate) with rollup;
+-------+--------+------+------+
| empid | custid | year | sum |
+-------+--------+------+------+
| 1 | a | 2010 | 30 |
| 1 | a | NULL | 30 |
| 1 | c | 2011 | 40 |
| 1 | c | NULL | 40 |
| 1 | NULL | NULL | 70 |
| 2 | a | 2009 | 10 |
| 2 | a | NULL | 10 |
| 2 | b | 2010 | 20 |
| 2 | b | NULL | 20 |
| 2 | NULL | NULL | 30 |
| 3 | a | 2010 | 10 |
| 3 | a | NULL | 10 |
| 3 | b | 2009 | 20 |
| 3 | b | 2011 | 30 |
| 3 | b | NULL | 50 |
| 3 | d | 2010 | 40 |
| 3 | d | NULL | 40 |
| 3 | NULL | NULL | 100 |
| 4 | a | 2011 | 20 |
| 4 | a | NULL | 20 |
| 4 | NULL | NULL | 20 |
| NULL | NULL | NULL | 220 |
+-------+--------+------+------+
22 rows in set (0.00 sec)
cube：mysql定义了cube关键字，但是不支持cube操作。
select empid,custid,year(orderdate),sum(qty) from t group by empid,custid,year(orderdate) with cube;
ERROR 1235 (42000): This version of MySQL doesn't yet support 'CUBE'
可以用rullup来模拟cube：（参考了大神姜承尧的文章）
select empid,custid,year(orderdate),sum(qty) from t group by empid,custid,year(orderdate) with rollup
union
select empid,custid,year(orderdate),sum(qty) from t group by empid,year(orderdate),custid with rollup
union
select empid,custid,year(orderdate),sum(qty) from t group by custid,year(orderdate),empid with rollup
union
select empid,custid,year(orderdate),sum(qty) from t group by custid,empid,year(orderdate) with rollup
union
select empid,custid,year(orderdate),sum(qty) from t group by year(orderdate),empid,custid with rollup
union
select empid,custid,year(orderdate),sum(qty) from t group by year(orderdate),custid,empid with rollup;
+-------+--------+-----------------+----------+
| empid | custid | year(orderdate) | sum(qty) |
+-------+--------+-----------------+----------+
| 1 | a | 2010 | 30 |
| 1 | a | NULL | 30 |
| 1 | c | 2011 | 40 |
| 1 | c | NULL | 40 |
| 1 | NULL | NULL | 70 |
| 2 | a | 2009 | 10 |
| 2 | a | NULL | 10 |
| 2 | b | 2010 | 20 |
| 2 | b | NULL | 20 |
| 2 | NULL | NULL | 30 |
| 3 | a | 2010 | 10 |
| 3 | a | NULL | 10 |
| 3 | b | 2009 | 20 |
| 3 | b | 2011 | 30 |
| 3 | b | NULL | 50 |
| 3 | d | 2010 | 40 |
| 3 | d | NULL | 40 |
| 3 | NULL | NULL | 100 |
| 4 | a | 2011 | 20 |
| 4 | a | NULL | 20 |
| 4 | NULL | NULL | 20 |
| NULL | NULL | NULL | 220 |
| 1 | NULL | 2010 | 30 |
| 1 | NULL | 2011 | 40 |
| 2 | NULL | 2009 | 10 |
| 2 | NULL | 2010 | 20 |
| 3 | NULL | 2009 | 20 |
| 3 | NULL | 2010 | 50 |
| 3 | NULL | 2011 | 30 |
| 4 | NULL | 2011 | 20 |
| NULL | a | 2009 | 10 |
| NULL | a | 2010 | 40 |
| NULL | a | 2011 | 20 |
| NULL | a | NULL | 70 |
|