Oracle多行函数

多行函数

SQL> --工资总额

SQL> select sum(sal) from emp;

 

SQL> --人数

SQL> select count(*) from emp;

 

SQL> select sum(sal)/count(*) 一,avg(sal) 二 from emp;

 

SQL> --null值 组函数会自动滤空；

SQL> select count(*),count(comm),count(nvl(comm,0)) from emp;

COUNT(*) COUNT(COMM) COUNT(NVL(COMM,0))

---------- ----------- ------------------

14 4 14

 

SQL> --多个列的分组: 先按照第一个列分组，如果相同，再按照第二列分组，以此类推

SQL> --求平均工资大于2000的部门

SQL> select deptno,avg(sal)

2 from emp

3 group by deptno

4 having avg(sal) > 2000;

DEPTNO AVG(SAL)

---------- ----------

20 2175

10 2916.66667

 

SQL> --where和having最大的区别：where后面不能使用组函数

SQL> --查询10号部门的平均工资

SQL> select deptno,avg(sal)

2 from emp

3 group by deptno

4 having deptno=10;

DEPTNO AVG(SAL)

---------- ----------

10 2916.66667

 

SQL> /*

SQL> 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> group by rollup(a,b)

SQL> =

SQL> group by a,b

SQL> +

SQL> group by a

SQL> +

SQL> group by null

SQL> */

 

SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);

DEPTNO JOB SUM(SAL)

---------- --------- ----------

10 CLERK 1300

10 MANAGER 2450

10 PRESIDENT 5000

10 8750

20 CLERK 1900

20 ANALYST 6000

20 MANAGER 2975

20 10875

30 CLERK 950

30 MANAGER 2850

30 SALESMAN 5600

30 9400

29025

SQL> --通过deptno分隔2行

SQL> 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

 

 

30 CLERK 950

MANAGER 2850

SALESMAN 5600

9400

 

 

29025

 

SQL> --不分隔

SQL> break on null

SQL> /