oracle分组排序汇总笔记.doc

一、相关函数GROUPBY、ROLLUP、CUBE、GROUPINGSETS、OVER、GROUPING_ID、GROUPING、DECODE、LAG、LEAD、RANK、DENSE_RANK、ROW_NUMBER、COUNT二、初始化数据CREATETABLECREATETABLEEMPLOYEEEIDNUMBERNOTNULL,ENAMEVARCHAR220NOTNULL,EADDRESSVARCHAR2200NOTNULL,E_DIDNUMBERNOTNULL,HIRE_DATEDATENOTNULL,SALARYNUMBER8,2NOTNULL,BONUSNUMBER8,2,BOSSNUMBERTABLESPACEUSERSPCTFREE10INITRANS1MAXTRANS255STORAGEINITIAL64MINEXTENTS1MAXEXTENTSUNLIMITEDCREATE/RECREATEPRIMARY,UNIQUEANDFOREIGNKEYCONSTRAINTSALTERTABLEEMPLOYEEADDPRIMARYKEYEIDUSINGINDEXINSERTCONTENTINSERTINTOEMPLOYEEEID,ENAME,EADDRESS,E_DID,HIRE_DATE,SALARY,BONUS,BOSSVALUES1, 郭芙 , 广东 ,1,TO_DATE 02012006 , DDMMYYYY ,20005,1005,10INSERTINTOEMPLOYEEEID,ENAME,EADDRESS,E_DID,HIRE_DATE,SALARY,BONUS,BOSSVALUES3, 杨康 , 成都 ,3,TO_DATE 14072004 , DDMMYYYY ,3000,NULL,10INSERTINTOEMPLOYEEEID,ENAME,EADDRESS,E_DID,HIRE_DATE,SALARY,BONUS,BOSSVALUES9, 杨过 , 广东 ,1,TO_DATE 02032005 , DDMMYYYY ,2000,1000,10INSERTINTOEMPLOYEEEID,ENAME,EADDRESS,E_DID,HIRE_DATE,SALARY,BONUS,BOSSVALUES10, 小龙女 , 广东 ,2,TO_DATE 05042000 , DDMMYYYY ,8000,NULL,10INSERTINTOEMPLOYEEEID,ENAME,EADDRESS,E_DID,HIRE_DATE,SALARY,BONUS,BOSSVALUES11, 郭襄 , 广东 ,3,TO_DATE 01122010 , DDMMYYYY ,5000,NULL,10INSERTINTOEMPLOYEEEID,ENAME,EADDRESS,E_DID,HIRE_DATE,SALARY,BONUS,BOSSVALUES14, 郭靖 , 成都 ,2,TO_DATE 08072008 , DDMMYYYY ,4300,NULL,14INSERTINTOEMPLOYEEEID,ENAME,EADDRESS,E_DID,HIRE_DATE,SALARY,BONUS,BOSSVALUES15, 黄蓉 , 成都 ,3,TO_DATE 13052009 , DDMMYYYY ,1600,200,14COMMIT三、具体应用1、GROUPBY与ROLLUPSELECTEADDRESSA,ENAMEB,SUMSALARYCFROMEMPLOYEEGROUPBYROLLUPEADDRESS,ENAME图1分析如图，这里不光只对第一个字段做了累计，先按EADDRESS,ENAME分组累计,再按EADDRESS分组累计，最后累计全部类似于SELECTFROMSELECTEADDRESS,ENAME,SUMSALARYAFROMEMPLOYEEGROUPBYEADDRESS,ENAMEUNIONALLSELECTEADDRESS,NULL,SUMSALARYFROMEMPLOYEEGROUPBYEADDRESSUNIONALLSELECTNULL,NULL,SUMSALARYFROMEMPLOYEE2、GROUPBY与CUBESELECTEADDRESSA,ENAMEB,SUMSALARYCFROMEMPLOYEEGROUPBYCUBEEADDRESS,ENAMEORDERBYA,B图2分析CUBE这里的使用与ROLLUP基本相同，但CUBE的合计更加详细，它能够显示次分组字段的合计信息类似于SELECTEADDRESSA,ENAMEB,SUMSALARYCFROMEMPLOYEEGROUPBYGROUPINGSETSEADDRESS,ENAME,EADDRESS,ENAME,ORDERBYA,B3、GROUPBY与GROUPINGSETSSELECTEADDRESSA,ENAMEB,SUMSALARYCFROMEMPLOYEEGROUPBYGROUPINGSETSEADDRESS,ENAME,图31SELECTEADDRESSA,ENAMEB,SUMSALARYCFROMEMPLOYEEGROUPBYGROUPINGSETSEADDRESS,ENAME,EADDRESS,图32分析GROUPBYGROUPINGSETS可以应用来指定自己感兴趣的总数组合。因为它不必计算它不需要集合。形式为GROUPBYGROUPINGSETSLIST,LIST，这里LIST是圆括号中的一个列序列，这个组合生成一个总数，所以要增加一个总和，必须增加一个NULL分组集。4、GROUPING结合DECODE的使用GROUPING()可接受一个列值，当列值为空时，函数返回1；如果列值非空，则返回0。(注意GROUPING只能在ROLLUP、CUBE查询中使用。还可以结合DECODE()函数来注释使用更好)DECODE相当于一条IF语句，如DECODEA,1,’SU’,2,’XIAO’,3,’DONG’,A表示A如果为1则返回SU,如果2就返回XIAO,如果3就返回DONG,否则返回A以图1为例SELECTDECODEGROUPINGEADDRESS,1,’合计’,EADDRESSA,ENAMEB,SUMSALARYCFROMEMPLOYEEGROUPBYROLLUPEADDRESS,ENAME图45、GROUPING_IDGROUPING_ID()函数可接受一列或多列，它返回GROUPING位向量的十进制值。GROUPING位向量的计算方法是按照顺序对每一列调用GROUPING()函数的结果组合起来。它的作用是借助HAVING子句对记录进行过滤，将不包含小计或总计的记录除去。GROUPING位向量的十进制值，我们由前面的介绍已知道当GROUPING()的列值为空时它返回1，当非空时返回0；比如EADDRESS,ENAME两列都为非空，GROUPING()都返回0。将这两列的值组合起来，形成一个位向量00，十进制为0。即，当EADDRESS,ENAME都非空时，GROUPING_ID()返回0。(这里要特别注意EADDR