oracle什么是多行函数,Oracle多行函数（组函数）

作用于多条数据 返回一条数据

SQL> --工资总额

SQL> select sum(sal) from emp;

SQL> --员工人数

SQL> select count(*) from emp;

SQL> --平均工资

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

SQL> --平均奖金

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

一        二        三

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

157.142857        550        550

SQL> --空值 4. 组函数会自动滤空

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

COUNT(*) COUNT(COMM)

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

14          4

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

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

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

14                14

SQL> --可以在组函数中嵌套滤空函数，来屏蔽组函数的滤空功能

SQL> --求各个部门的平均工资

SQL> select deptno,avg(sal)

2  from emp

3  group by deptno;

group by depnto

SQL> --语法

SQL> select deptno,job,avg(sal)

2  from emp

3  group by depnto;

group by depnto

*

第 3 行出现错误:

ORA-00904: "DEPNTO": 标识符无效

SQL>--在Oracle中所有没有被组函数包括的列 必须出现在group by的后面

SQL> --group by多列：先按照第一列分；如果第一列相同，再按照第二列分

SQL> ed

已写入 file afiedt.buf

1  select deptno,job,avg(sal)

2  from emp

3  group by deptno,job

4* order by 1

SQL> /

DEPTNO JOB        AVG(SAL)

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

10 CLERK          1300

10 MANAGER        2450

10 PRESIDENT      5000

20 ANALYST        3000

20 CLERK            950

20 MANAGER        2975

30 CLERK            950

30 MANAGER        2850

30 SALESMAN        1400

已选择9行。

SQL> --按部门，不同的职位统计平均工资

SQL> --having

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

SQL> ed

已写入 file afiedt.buf

1  select deptno,avg(sal)

2  from emp

3  group by deptno

4* having avg(sal)>2000

SQL> /

SQL> --having 过滤分组

SQL> --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> select deptno,avg(sal)

2  from emp

3  where deptno=10

4  group by deptno;

DEPTNO  AVG(SAL)

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

10 2916.66667

SQL> --SQL优化  3. 尽量使用where

SQL> --            例外：如果条件中含义组函数，只能使用having

制作报表是的group by 语句增强

SQL> --group by的增强

SQL> /*

SQL> group by deptno,job

SQL> +

SQL> group by deptno

SQL> +

SQL> group by null

SQL>

SQL> =

SQL>

SQL> group by rollup(deptno,job)

SQL> group by rollup(a,b)

SQL> =

SQL> group by a,b

SQL> +

SQL> group by a

SQL> +

SQL> group by null

SQL>

SQL> */

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

DEPTNO JOB        SUM(SAL)

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

30                9400

29025

已选择13行。

SQL> --SQLPLUS支持报表功能

SQL> break on deptno skip 2

SQL> select deptno,job,sum(sal)

2  from emp

3  group by rollup(deptno,job);

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

已选择13行。

SQL> break on null

SQL> /

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

DEPTNO JOB        SUM(SAL)

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

30                9400

29025

已选择13行。