oracle 组函数

oracle 组函数

 

 

 

group by的增强
SQL> select deptno,job,sum(sal) from emp group by deptno,job
SQL> +
SQL> select deptno,sum(sal) from emp group by deptno
SQL> +
SQL> select sum(sal) from emp
SQL> 
SQL> ===
SQL> 
SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);
SQL> 
SQL> 抽象
SQL> group by rollup(a,b)
SQL> ==
SQL> group by a,b
SQL> +
SQL> group by a
SQL> +
SQL> group by null
SQL> 
SQL> */
SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);

 

 

报表

break on deptno skip 2
SQL> /

    DEPTNO JOB         SUM(SAL)                                                                                                                                                                         
---------- --------- ----------                                                                                                                                                                         
        10 CLERK           1300                                                                                                                                                                         
           MANAGER         2450                                                                                                                                                                         
           PRESIDENT       5000                                                                                                                                                                         
                           8750                                                                                                                                                                         
                                                                                                                                                                                                        
                                                                                                                                                                                                        
        20 CLERK           1900                                                                                                                                                                         
           ANALYST         6000                                                                                                                                                                         
           MANAGER         2975                                                                                                                                                                         
                          10875                                                                                                                                                                         
                                                                                                                                                                                                        

    DEPTNO JOB         SUM(SAL)                                                                                                                                                                         
---------- --------- ----------                                                                                                                                                                         
                                                                                                                                                                                                        
        30 CLERK            950                                                                                                                                                                         
           MANAGER         2850                                                                                                                                                                         
           SALESMAN        5600                                                                                                                                                                         
                           9400                                                                                                                                                                         
                                                                                                                                                                                                        
                                                                                                                                                                                                        
                          29025                                                                                                                                                                         
                                                                                                                                                                                                        
                                                                                                                                                                                                        

 

posted @ 2017-04-16 08:50 酸奶加绿茶 阅读( ...) 评论( ...) 编辑 收藏