oracle组函数知识点,Oracle学习(四)：组函数

1.知识点：可以对照下面的录屏进行阅读

SQL> --组函数类型：avg，count，max，min，sum

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;

SQL> --造成一方法和二三方法结果不同的原因：空值 4. 组函数会自动滤空，只统计不为空

SQL> select count(*),count(comm) from emp;--结果不同，count(comm)只统计comm不为空的个数

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

SQL> select count(*),count(nvl(comm,0)) from emp; --结果相同，count(comm)统计所有结果

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

SQL> select deptno,avg(sal)

2 from emp

3 group by deptno;

SQL> --语法

SQL> --在select列表中所有未包含在组函数中的列都应该包含在group by子句中

SQL> --包含在group by子句中的列不必包含在select列表中

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

SQL> select deptno,job,avg(sal)

2 from emp

3 group by deptno,job

4 order by 1

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

SQL> --having：过滤分组

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

SQL>select deptno,avg(sal)

2 from emp

3 group by deptno

4 having avg(sal)>2000

SQL> --having和where的区别

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

SQL> select deptno,avg(sal)

2 from emp

3 group by deptno

4 having deptno=10;

SQL> --SQL优化 3. 尽量使用where，例外：如果条件中含有组函数，只能使用having

SQL> --group by的增强：做部门报表可能用到

SQL> -- group by deptno,job + group by deptno + group by null = group by rollup(deptno,job)

SQL> -- group by rollup(a,b) = group by a,b + group by a + group by null

SQL> select deptno,job,sum(sal)

2 from emp

3 group by rollup(deptno,job);

SQL> --SQLPLUS支持报表功能

SQL> break on deptno skip 2 --break on deptno：按照部门号分段，相同的部门号只显示一次；skip 2：不同的部门之间空2行

2.在Sqlplus下实际执行的结果录屏：

SQL> host cls

SQL> --工资总额

SQL> select sum(sal) from emp;

SUM(SAL)

----------

29025

SQL> --员工人数

SQL> select count(*) from emp;

COUNT(*)

----------

14

SQL> --平均工资

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

一 二

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

2073.21429 2073.21429

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> host cls

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

SQL> set linesize 150

SQL> col sal for 9999

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 17-12月-80 800 20

7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30

7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30

7566 JONES MANAGER 7839 02-4月 -81 2975 20

7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30

7698 BLAKE MANAGER 7839 01-5月 -81 2850 30

7782 CLARK MANAGER 7839 09-6月 -81 2450 10

7788 SCOTT ANALYST 7566 13-7月 -87 3000 20

7839 KING PRESIDENT 17-11月-81 5000 10

7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30

7876 ADAMS CLERK 7788 13-7月 -87 1100 20

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7900 JAMES CLERK 7698 03-12月-81 950 30

7902 FORD ANALYST 7566 03-12月-81 3000 20

7934 MILLER CLERK 7782 23-1月 -82 1300 10

已选择14行。

SQL> select deptno,avg(sal)

2 from emp

3 group by depnto;

group by depnto

*

第 3 行出现错误:

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

SQL> ed

已写入 file afiedt.buf

1 select deptno,avg(sal)

2 from emp

3* group by deptno

SQL> /

DEPTNO AVG(SAL)

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

30 1566.66667

20 2175

10 2916.66667

SQL> --语法

SQL> select deptno,job,avg(sal)

2 from emp

3 group by depnto;

group by depnto

*

第 3 行出现错误:

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

SQL> ed

已写入 file afiedt.buf

1 select deptno,job,avg(sal)

2 from emp

3* group by deptno

SQL> /

select deptno,job,avg(sal)

*

第 1 行出现错误:

ORA-00979: 不是 GROUP BY 表达式

SQL> ed

已写入 file afiedt.buf

1 select deptno,job,avg(sal)

2 from emp

3* group by deptno,job

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> host cls

SQL> --having

SQL> select deptno,avg(sal)

2 from emp

3 group by depnto;

group by depnto

*

第 3 行出现错误:

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

SQL> ed

已写入 file afiedt.buf

1 select deptno,avg(sal)

2 from emp

3* group by deptno

SQL> /

DEPTNO AVG(SAL)

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

30 1566.66667

20 2175

10 2916.66667

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

DEPTNO AVG(SAL)

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

20 2175

10 2916.66667

SQL> --having 过滤分组

SQL> host cls

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

SP2-0734: 未知的命令开头 "例外：如果..." - 忽略了剩余的行。

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

SQL> host cls

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>

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)

2 from emp

3 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

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行。

SQL> spool off

原文：http://blog.csdn.net/v123411739/article/details/29469693