oracle 组函数

 

 

 完整的select **  from **  where  ** group by ** having ** order by **;

select job_id,sum(salary) payroll 
from employees
where job_id not like '%REP%'
group by job_id
having sum(salary)>1300
order by sum(salary)

　　

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