Oracle group by 用法实例详解.docx
- 文档编号:10465454
- 上传时间:2023-05-26
- 格式:DOCX
- 页数:7
- 大小:17.38KB
Oracle group by 用法实例详解.docx
《Oracle group by 用法实例详解.docx》由会员分享,可在线阅读,更多相关《Oracle group by 用法实例详解.docx(7页珍藏版)》请在冰点文库上搜索。
Oraclegroupby用法实例详解
Groupby的语法
Select[filed1,fild2,]聚合函数(filed),
[Grouping(filed),]
[Grouping_id(filed1,filed2,…)]
Fromtablename
Wherecondition
[Groupby{rollup|cube}(filed,filed2)]
[havingcondition]
[orderbyfiled1]
一、基本用法:
(1)我们通过几个例子来研究groupby的基本用法
创建测试表
SQL>createtablesales(
2empidnumber,--雇员ID
3depidnumber,--部门ID
4areavarchar(20),--区域
5salenumnumber);--销售额
表已创建。
SQL>insertintosalesvalues(1,1,'china',10);
SQL>insertintosalesvalues(2,1,'china',10);
SQL>insertintosalesvalues(3,1,'china',10);
SQL>insertintosalesvalues(3,1,'china',10);
SQL>insertintosalesvalues(3,1,'china',10);
SQL>insertintosalesvalues(1,1,'china',10);
SQL>insertintosalesvalues(2,1,'china',10);
SQL>insertintosalesvalues(4,2,'china',10);
SQL>insertintosalesvalues(4,2,'china',10);
SQL>insertintosalesvalues(5,3,'us',10);
SQL>insertintosalesvalues(5,3,'us',10);
需求1,按部门统计销售额(简单用法)
SQL>selectdepid,sum(salenum)fromsalesgroupbydepid;
DEPIDSUM(SALENUM)
----------------------
1 70
2 20
3 20
需求2,按部门统计销售额,并且只显示销售总额小于30的部门及销售额(使用having子句)
SQL>selectdepid,sum(salenum)totalnumfromsales
groupbydepid
havingsum(salenum)<30;
DEPIDSUM(SALENUM)
----------------------
2 20
3 20
注解:
需求2需要使用having字名,而且在子句中不能使用别名,必须使用在select语句中书写的形式
(2)Where和having的区别
Wheret和having子句都用来筛选数据,但是where是针对原数据进行筛选,而having子句只是针对汇总后的结果进行筛选,所以在需求二的例子中,想要对销售总额进行过滤只能使用having子句
(3)使用orderby排序
SQL>selectdepid,sum(salenum)fromsalesgroupbydepid;
DEPIDSUM(SALENUM)
----------------------
1 70
2 20
3 20
注意观察需求1的例子,depid是已经按照在depid升序排列的,这是因为oracle在做聚合统计的时候会首先对字段进行排序,所以最终的结果是按照升序进行排列的,如果orderby后跟着多个字段,默认排序是先对第一个字段升序排序,然后再排第二个字段,以此类推,所以如果在应用中仅仅需要长序排序可以不用加orderby参数,毕竟这会影响性能
二、扩展用法:
扩展用法使用下面的表进行实验研究
SQL>createtabletestgroup(
2avarchar(5),
3bvarchar(5),
4cvarchar(5),
5nnumber);
建完测试表,然后插入两条测试数据
SQL>insertintotestgroupvalues('a1','b1','c1',10);
SQL>insertintotestgroupvalues('a1','b1','c1',20);
我们使用基本的groupby可以得到以下结果
SQL>selecta,b,c,sum(n)totalfromtestgroupgroupbya,b,c;
A B C TOTAL
-------------------------
a1 b1 c1 30
(1)使用rollup操作符
Rollup意思有”卷起,汇总”的意思,他可以在使得在其括号中的字段,按从右到左的顺序分别group后显示,类似我们用多个groupby语句,然后unionall起来,我们把针对上面的测试表,使用rollup操作符,看看效果
SQL>selecta,b,c,sum(n)totalfromtestgroupgroupbyrollup(a,b,c);
Result:
A
B
C
TOTAL
a1
b1
c1
30
a1
b1
30
a1
30
30
从上面结果可以看出,除了对(a1,b1,c1)进行了汇总外,又从右向左分别对子句中的”a,b,c”字段进行了汇总,例如(a1,b1),(a1)()
(2)使用cube操作符
Cube意思是立方,使用该操作符可以对操作符内的字段,进行遍历组合汇总,例如cube(a,b,c),那么就会产生8种组合结果,分别如下”a-b-c”,”a-b”,”a”,”a-c”,”b-c”,”b”,”c”,”空”,看下面的例子
SQL>selecta,b,c,sum(n)totalfromtestgroupgroupbycube(a,b,c);
Result:
A
B
C
TOTAL
30
c1
30
b1
30
b1
c1
30
a1
30
a1
c1
30
a1
b1
30
a1
b1
c1
30
(3),使用grouping(filed)函数
使用grouping函数必须先理解rollup和cube操作符,那么grouping函数有什么用呢?
在日常应用中,我们通过rollup或者cube对汇总进行了汇总,汇总后的结果往往要传送到应用程序端,在应用程序端我们必须要有一个依据来判断某行数据是不是按照rollup或cube进行汇总,grouping函数可以用来产生这个依据,他可以接收一个参数,判断该参数是否为null,是则返回1,否则返回0,我样可以据此来判断该是否按某列进行汇总统计的,当然在实验应用中,0和1看起来不那么直观,我们可以使用decode或者case函数进行转议,让查看结果看起来更直观,请看以下例子
SQL>selectgrouping(a)ca,grouping(b)cb,grouping(c)cc,a,b,c,sum(n)fromtestgroupgroupbyrollup(a,b,c);
Result:
CA
CB
CC
A
B
C
SUM(N)
0
0
0
a1
b1
c1
30
0
0
1
a1
b1
30
0
1
1
a1
30
1
1
1
30
(4)使用grouping_id(filed1,file2,…)函数
使用grouping函数有时候感觉不是那么灵活的,他只能接收一个字段,而grouping_id()函数则可以接收多个字段,GROUPING_ID()函数可以接受一列或多列,返回按GROUPING位向量进行计算的十进制值。
下面我们通过一个例子来研究grouping_id函数是如何按照grouping函数的位向量进行计算的。
SQL>selectgrouping(a)ca,grouping(b)cb,grouping_id(a,b)caandb,a,b,sum(n)fromtestgroupgroupbyrollup(a,b);
Result:
CA
CB
CAANDB
A
B
SUM(N)
0
0
0
a1
b1
30
0
1
1
a1
30
1
1
3
30
位向量计算方法:
如上例,
第一行,CA=0,CB=0,那么位向量就是“00”,换算成十进制是0
第二行,CA=0,CB=1,那么位向量就是‘01’,换算成十进制是1
第三行,CA=1,CB=1,那么位向量就是‘11’,换算成十进制是3
请注意上例中grouping_id的计算值跟括号内的字段顺序有关,上例中书写顺序是grouping_id(a,b),a字段在前面,如果换下顺序grouping_id(b,a),计算结果是不一样的,看下例
SQL>selectgrouping(a)ca,grouping(b)cb,grouping_id(b,a)caandb,a,b,sum(n)fromtestgroupgroupbyrollup(a,b);
Result:
CA
CB
CAANDB
A
B
SUM(N)
0
0
0
a1
b1
30
0
1
2
a1
30
1
1
3
30
看看第二行红色字体,grouping_id中的字段顺序发生了变化,位向量值也不一样了
(4-1)grouping_id()函数的用途
上面讲了grouping_id的用法,但在日常工作中,我们如何应用该函数呢?
其实只要了解了他的原理及用法,要怎么用,就看我们是否可以灵活使用了,下面介绍一种常见的用法:
假如我们要对某testgroup进行分组统计,并且过滤掉不包括小计或总计的行,这时grouping_id就有用武之地了,我们可以利用grouping_id的值结合having子句,通过判断grouping_id是否大于0来过滤掉不需要的行。
SQL>selectgrouping(a)ca,grouping(b)cb,grouping_id(a,b)caandb,a,b,sum(n)fromtestgroupgroupbyrollup(a,b)havinggrouping_id(a,b)>0;
Result:
CA
CB
CAANDB
A
B
SUM(N)
0
1
1
a1
30
1
1
3
30
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle group by 用法实例详解 用法 实例 详解