Oracle_SQL_常用函数选读.docxVIP

Oracle SQL 一些函数用法以下sql环境都是在 oracle 11g/scott完成Group by与GROUP BY 一起使用的关建字GROUPING,GROUP SET,ROLLUP,CUBE结合这些特性的函数可以实现SQL的明细+分组统计GROUPINGGROUPING记录是对哪个字段进行统计,其值只能是 0 1Group(column) =0 表示此字段参与了分组统计 =1表示字段未参与分组统计selectdeptno,job,sum(sal),grouping(deptno),grouping(job) fromempgroupby ( deptno,job);执行结果如下ROLLUPselectdeptno,job,sum(sal),grouping(deptno),grouping(job) fromempgroupbyrollup( deptno,job);等价于selectnull,null,sum(sal) fromempunionallselectdeptno,null,sum(sal) fromempgroupbydeptnounionallselectdeptno,job,sum(sal) fromempgroupbydeptno,job首先是进行无字段的聚合,然后在对字段进行从左到右依次组合后聚合从上面的结果可以看到rollup(deptno,job) = group by deptno, job + group by deptno + group by null另外 rollup (deptno,job)rollup(job,deptno) 两者的结果集是不同的,可以把SQL修改后,执行下看看提示:在对rollup的分组进行组合使用selectdeptno,job,sum(sal),grouping (deptno),grouping(job) fromempgroupbyrollup ((deptno,job),null) ;此时 rollup 对(deptno,job)这个组合字段进行分组,这样我们可以控制rollup的分组规则的颗粒细度rollup ((deptno,job),null) =group by (deptno,job) + group by (deptno,job),null+group by null其中group by (deptno,job)=group by (deptno,job),null所以其产生的结果集会出现group by (deptno,job)的重复记录CUBEselectdeptno,job,sum(sal),grouping (deptno),grouping(job) fromempgroupbycube( deptno,job);等价于selectdeptno,job,sum(sal) fromempgroupbydeptno,jobunionallselectdeptno,null,sum(sal) fromempgroupbydeptnounionallselectnull,null,sum(sal) fromempunion allselectnull,job,sum(sal) from emp group by jobCUBE的语法规则与rollup一致,只是在分组聚合上cube要更复杂,从数学的角度讲一个是排序规则,一个是组合规则GROUP SETS可以按自己定义的规则进行分组聚合,Grouping sets () 的聚合规则是按“()”里的字段(字段可以是表的单一字段,也可以是多个字段的组合)进行聚合selectdeptno,job,sum(sal),grouping (deptno),grouping(job) fromempgroupbygroupingsets(deptno,job,null) ;等价于 group by depnto + group by job +group by nullselectdeptno,job,sum(sal),grouping (deptno),grouping(job) fromempgroupbygroupingsets((deptno,job),null) ;等价于 group by deptno,job +group by nullselectdeptno,job,sum(sal),grouping (deptno),grouping(job) fromempgroupbygroupingsets ((deptno,job)) ;等价与 group bydeptno,jobConnect byCONNECT BY 语句特有的函数SYS_CONNECT_BY_PATH