mysql和oracle的group by的rollup和cube

最新推荐文章于 2021-04-02 20:12:14 发布

娜然

最新推荐文章于 2021-04-02 20:12:14 发布

阅读量1.1k

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/nanaranran/article/details/77649794

版权

mysql 专栏收录该内容

25 篇文章 0 订阅

订阅专栏

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 |
| NULL | b | 2009 | 20 |
| NULL | b | 2010 | 20 |
| NULL | b | 2011 | 30 |
| NULL | b | NULL | 70 |
| NULL | c | 2011 | 40 |
| NULL | c | NULL | 40 |
| NULL | d | 2010 | 40 |
| NULL | d | NULL | 40 |
| NULL | NULL | 2009 | 30 |
| NULL | NULL | 2010 | 100 |
| NULL | NULL | 2011 | 90 |
+-------+--------+-----------------+----------+
45 rows in set (0.00 sec)

oracle就比较厉害了，都实现了。
----------------------------------------------------------------------
下面是创建测试表：
create table t(
orderid int not null,
orderdate date not null,
empid int not null,
custid varchar(10) not null,
qty int not null,
primary key(orderid,orderdate)
);
insert into t select 1,'2010-01-02',3,'a',10 from dual;
insert into t select 2,'2010-04-02',2,'b',20 from dual;
insert into t select 3,'2010-05-02',1,'a',30 from dual;
insert into t select 4,'2010-07-02',3,'d',40 from dual;
insert into t select 5,'2011-01-02',4,'a',20 from dual;
insert into t select 6,'2011-01-02',3,'b',30 from dual;
insert into t select 7,'2011-01-02',1,'c',40 from dual;
insert into t select 8,'2009-01-02',2,'a',10 from dual;
insert into t select 9,'2009-01-02',3,'b',20 from dual;

oracle的rollup：
select to_char(orderdate,'yyyy') as year,sum(qty) as sum from t group by rollup(to_char(orderdate,'yyyy'));

YEAR SUM
---- ----------
2009 30
2010 100
2011 90
220
Elapsed: 00:00:00.04

select empid,custid,to_char(orderdate,'yyyy') year,sum(qty) sum
from t
group by rollup(empid,custid,to_char(orderdate,'yyyy'));

EMPID CUSTID YEAR SUM
---------- ---------- ---- ----------
1 a 2010 30
1 a 30
1 c 2011 40
1 c 40
1 70
2 a 2009 10
2 a 10
2 b 2010 20
2 b 20
2 30
3 a 2010 10
3 a 10
3 b 2009 20
3 b 2011 30
3 b 50
3 d 2010 40
3 d 40
3 100
4 a 2011 20
4 a 20
4 20
220

22 rows selected.

oracle的cube：

select empid,custid,to_char(orderdate,'yyyy'),sum(qty) from t group by cube(empid,custid,to_char(orderdate,'yyyy'));

EMPID CUSTID TO_C SUM(QTY)
---------- ---------- ---- ----------
220
2009 30
2010 100
2011 90
a 70
a 2009 10
a 2010 40
a 2011 20
b 70
b 2009 20
b 2010 20
b 2011 30
c 40
c 2011 40
d 40
d 2010 40
1 70
1 2010 30
1 2011 40
1 a 30
1 a 2010 30
1 c 40
1 c 2011 40
2 30
2 2009 10
2 2010 20
2 a 10
2 a 2009 10
2 b 20
2 b 2010 20
3 100
3 2009 20
3 2010 50
3 2011 30
3 a 10
3 a 2010 10
3 b 50
3 b 2009 20
3 b 2011 30
3 d 40
3 d 2010 40
4 20
4 2011 20
4 a 20
4 a 2011 20

45 rows selected.

结果相同。