mysql group by cube_group by cube和rollup学习笔记

建表如下：

create table TEST_GROUP(NAME VARCHAR2(20),CLASS VARCHAR2(10),KEMU VARCHAR2(4),CHENGJI VARCHAR2(3));

insert into TEST_GROUP (NAME, CLASS, KEMU, CHENGJI)

values ('张三', '1班', '数学', '90');

insert into TEST_GROUP (NAME, CLASS, KEMU, CHENGJI)

values ('张三', '1班', '语文', '68');

insert into TEST_GROUP (NAME, CLASS, KEMU, CHENGJI)

values ('李四', '1班', '数学', '90');

insert into TEST_GROUP (NAME, CLASS, KEMU, CHENGJI)

values ('李四', '1班', '语文', '88');

insert into TEST_GROUP (NAME, CLASS, KEMU, CHENGJI)

values ('王五', '2班', '数学', '70');

insert into TEST_GROUP (NAME, CLASS, KEMU, CHENGJI)

values ('王五', '2班', '语文', '88');

insert into TEST_GROUP (NAME, CLASS, KEMU, CHENGJI)

values ('小六', '2班', '数学', '95');

insert into TEST_GROUP (NAME, CLASS, KEMU, CHENGJI)

values ('小六', '2班', '语文', '98');

ROLLUP用法：

GROUP BY ROLLUP(A, B, C)， 首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。

select class, name, sum(chengji) from test_group

group by rollup(class, name) order by class;

CUBE用法：

GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)， 最后对全表进行GROUP BY操作。

select class, name, sum(chengji) from test_group

group by cube(class, name) order by class;

grouping sets用法：

grouping sets就是对参数中的每个参数做grouping，也就是有几个参数做几次grouping, 例如使用group by grouping sets(A,B,C)，则对(A),(B),(C)进行group by，如果使用group by grouping sets((A,B),C),则对(A,B),(C)进行group by。甚至grouping by grouping set(A,A)都是语法允许的，也就是对(A)进行2次group by, grouping sets的参数允许重复

select class, name, sum(chengji) from test_group

group by grouping sets(class,name);

grouping()函数排除空值用法:

grouping():参数只有一个，而且必须为group by中出现的某一列，表示结果集的一行是否对该列做了grouping。对于对该列做了grouping的行而言，grouping()=0，反之为1；

select case

when grouping(name) = '1' and grouping(class) = '0' then

class || ' 合计'

when grouping(name) = '0' and grouping(class) = '0' then

name

when grouping(name) = '1' and grouping(class) = '1' then

'总计'

end,

sum(chengji)

from test_group

group by rollup(class, name)

order by class;

having grouping()用法：

select decode(grouping(name), '1', ' 总计:', name), sum(chengji)

from test_group

group by cube(class, name)

having grouping(class) = 1;

grouping_id()函数用法:

grouping_id():参数可以是多个，但必须为group by中出现的列。Grouping_id()的返回值其实就是参数中的每列的grouping()值的二进制向量，例如如果grouping(A)=1，grouping(B)=0，则grouping_id(A,B)的返回值就是二进制的10，转成10进制就是2。

select grouping_id(class, name),

grouping(class),

grouping(name),

name,

class,

sum(chengji)

from test_group

group by cube(class, name);

结果集看是有点乱，我们加上having条件看看：

select grouping_id(class, name),

grouping(class),

grouping(name),

name,

class,

sum(chengji)

from test_group

group by cube(class, name)

having grouping_id(class, name) in(0,1,3);

group_id()函数用法:

group_id():无参数。见上面的说明3)，group by对某些列的集合会进行重复的grouping，而实际上绝大多数情况下对结果集中的这些重复行是不需要的，那就必须有办法剔出这些重复grouping的行。当结果集中有n条重复grouping而形成的行时，每行的group_id()分别是0,1,…,n，这样我们在条件中加入一个group_id()<1就可以剔出这些重复grouping的行了。

GROUP BY 的局限性

如果不知道GROUP BY不能做什么，你对于它的学习就是不完整的。GROUP BY的局限性如下：

1. LOB列，不能用做GROUP BY 表达式的一部分

2. 子查询是不允许的

3. 如果GROUP BY子句引用任何对象类型的列，则查询不能并行化

大小: 29.5 KB

大小: 38.1 KB

大小: 25 KB

大小: 35.4 KB

大小: 28 KB

大小: 72.2 KB

大小: 56.3 KB

分享到：

2012-10-23 19:49

浏览 474

分类:数据库

评论

发表评论

文章已被作者锁定，不允许评论。