oracle分组高级,Oracle高级查询--分组查询篇

Oracle高级查询--分组查询篇，什么是分组函数？

分组函数作用于一组数据，并对一组数据返回一个值。

常用的分组函数为AVG、SUM、MIN、MAX、COUNT、WM_CONCAT

1.1 Oracle分组函数1

使用AVG(平均值)和SUM(合计)函数

select avg(sal),sum(sal) from emp;

使用MIN(最小值)和MAX(最大值)函数

select max(sal),min(sal) from emp;

使用COUNT(计数)函数

select count(*) from emp;

使用DISTINCT(distinct)关键字,DISTINCT用于去掉重复的记录

select distinct deptno from emp;

1.2 Oracle分组函数2

使用WM_CONCAT:行转列

set linesize 200 //说明命令输出结果在一行最多100个字符，超过一百个字符就换行

col 部门中员工的名字 for a60 //用来定义列的宽度

select deptno 部门号，wm_concat(ename) 部门中员工的名字 from emp group by deptno;

1.3 Oracle分组函数3

分组函数与空值

分组函数会自动忽略空值

注意：NVL函数使分组函数无法忽略空值

举例1：统计员工的平均工资

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

1.4 group by子句的使用

可以使用group by 子句将表中的数据分成若干组

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

示例：求每个部门的平均工资，要求显示：部门号，部门的平均工资

select deptno,avg(sal) from emp group by deptno;

使用多个列分组

示例：按部门、不同的职位，统计员工的工资总额

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

1.5 having字句的使用以及和where的区别

过滤分组

select deptno ,avg(sal) from emp group by deptno having avg(sal) >2000 //平均数大于2000

where与having的区别：

不能在where子句中使用组函数(注意)。

可以在having子句中使用组函数

where和having可以通用的情况

示例：查询10号部门的平均工资

select deptno,avg(sal)from emp group by deptno having = 10

注意：从sql优化的角度上看，尽量使用where

having先分组后过滤 where先过滤后分组

1.6在分组函数中使用Order by

order by 的作用是用于排序

asc升序 desc降序

1.7分组函数的嵌套

示例：求部门平均工资的最大值

select max(avg(sal)) from emp group by deptno;

1.8 group by语句的增强

select deptno ,job,sum(sal) from emp group by deptno,job

+

select deptno, sum(sal) from emp group by depnto

+

select sum(sal) from emp

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

语法：group by rollup(a,b) 等价于 group by a,b + group by a + group by null

1.9 SQL*Plus的报表功能

报表包括：标题、页码、别名等

title col 15 ‘我的报表’ col 35 sql.pno //col 15 表示空15个列；sql.pno表示报表的页码

col deptno heading 部门号

col job heading 职位

col sum(sal)heading 工资总额

break on deptno skip 1