增强group by

最新推荐文章于 2022-11-18 21:29:51 发布

cml2016

最新推荐文章于 2022-11-18 21:29:51 发布

阅读量147

点赞数

文章标签： java

总结：
1.提示当使用group by时，未在group by部分用到的表列在select部分出现时必须使用分组函数。
2.having子句允许用户指定对一个记录组的搜索条件。而通常的where查询条件只针对单记录，不针对记录组。
3.在Group By子句中，必须采用表达式的全称，而不能够采用其别名.
4.采用Group语句会自动对纪录进行排序。在使用Order By排序子句对统计结果进行重新排序的时候，要慎重。
5.如果想把某个字段当作第一顺序排序的话，则只需要把这个字段放置在分组语句中的第一个参数即可。

示例：

[java]view plaincopyprint? 
    
 --聚合函数，不能出现在where子句中，比如where avg(salary)>4000   
 select t.deptno,sum(t.sal) from emp t group by t.deptno having sum(t.sal)>9000;   
    
 DEPTNO SUM(T.SAL)   
 ------ ----------   
     30       9400   
     20      10875   

[java]view plaincopyprint? 
    
 --查出表中name重复大于10条的记录   
 select name from test group by name having count(*)>10;   

[java]view plaincopyprint? 
    
 --Rollup分组产生的结果集包含常规的分组和小计行。在生成包含小计和合计的报表时，ROLLUP 运算符很有用。   
 --使用grouping函数标识rollup或者cube创建的行   
 --用GROUPING函数模拟能够发现在一行中的构成小计的分组,返回0或者1。   
 --GROUPING函数返回0意味着下列情况：这个表达式包含在小计中.   
 --GROUPING函数返回1意味着下列情况：这个表达式没有包含在小计中.   
 select decode(grouping(t.deptno),1,'10/20/30',t.deptno) deptno,   
        case   
          when grouping(t.ename)=1 and grouping(t.deptno)=0 then '小计'   
          when grouping(t.ename)=1 and grouping(t.deptno)=1 then '合计'   
          else t.ename   
        end ename,   
        sum(t.sal) sal    
 from emp t group by rollup(t.deptno,t.ename);   
    
 DEPTNO                                   ENAME             SAL   
 ---------------------------------------- ---------- ----------   
 10                                       KING             5000   
 10                                       CLARK            2450   
 10                                       MILLER           1300   
 10                                       小计             8750   
 20                                       FORD             3000   
 20                                       ADAMS            1100   
 20                                       JONES            2975   
 20                                       SCOTT            3000   
 20                                       SMITH             800   
 20                                       小计            10875   
 30                                       WARD             1250   
 30                                       ALLEN            1600   
 30                                       BLAKE            2850   
 30                                       JAMES             950   
 30                                       MARTIN           1250   
 30                                       TURNER           1500   
 30                                       小计             9400   
 10/20/30                                 合计            29025   

[java]view plaincopyprint? 
    
 --Cube操作依据条件中列的所有可能组合情况进行分组，并对每一个分组返回一行汇总。CUBE是一个类似ROLLUP的扩展，使得可以用一个语句计算所有可能的聚合。   
 select t.deptno,sum(t.sal) sal from emp t group by cube (t.deptno);   
    
 DEPTNO        SAL   
 ------ ----------   
             29025   
     10       8750   
     20      10875   
     30       9400   

[java]view plaincopyprint? 
    
 --使用grouping sets产生一个单独的结果集（等价于union all的方式）   
 --GROUPING SETS是GROUP BY子句的扩展，可以在一个查询中定义多个组。   
 --Oracle服务器计算所有在GROUPING SETS子句中指定的组，并且把每个单独的组产生的结果进行UNION ALL操作。   
 --GROUPING SET的效率：只需要一次全表扫描，不需要写复杂的UNION语句，同时GROUPING SETS子句中含有的元素越多，得到的性能增益越大。   
 select t.deptno,t.ename,t.job from emp t group by grouping sets((t.deptno,t.ename),(t.deptno,t.job));   
    
 DEPTNO ENAME      JOB   
 ------ ---------- ---------   
     20 JONES         
     30 WARD          
     20 SCOTT         
     10 KING          
     30 JAMES         
     30 ALLEN         
     30 MARTIN        
     30 BLAKE         
     20 FORD          
     20 SMITH         
     20 ADAMS         
     10 MILLER        
     10 CLARK         
     30 TURNER        
     20            CLERK   
     30            SALESMAN   
     20            MANAGER   
     30            CLERK   
     10            PRESIDENT   
     30            MANAGER   
     10            CLERK   
     10            MANAGER   
     20            ANALYST   
        
 select t.deptno,t.ename from emp t group by (t.deptno,t.ename);   
    
 DEPTNO ENAME   
 ------ ----------   
     20 JONES   
     30 WARD   
     20 SCOTT   
     10 KING   
     30 JAMES   
     30 ALLEN   
     30 MARTIN   
     30 BLAKE   
     20 FORD   
     20 SMITH   
     20 ADAMS   
     10 MILLER   
     10 CLARK   
     30 TURNER   
    
 select t.deptno,t.job from emp t group by (t.deptno,t.job);   
    
 DEPTNO JOB   
 ------ ---------   
     20 CLERK   
     30 SALESMAN   
     20 MANAGER   
     30 CLERK   
     10 PRESIDENT   
     30 MANAGER   
     10 CLERK   
     10 MANAGER   
     20 ANALYST   