分组函数的格式:
SELECT [column,] group_function(column)
FROM table
[WHERE condition]
[GROUP BY column]
[HAVING group_function(column)expression
[ORDER BY column| group_function(column)expression];
5个常用的分组函数
MIN
MAX
min和max可以用于任何数据类型
查询入职日期最早和最晚的日期
select min(hiredate), max(hiredate) from emp;
SUM
AVG
sum和avg函数只能够对数值类型的列或表达式操作
查询职位以SALES开头的所有员工的平均工资、最低工资、最高工资、工资和
select avg(sal),max(sal),min(sal),sum(sal) from emp
where job like 'SALES%';
COUNT
count函数的主要功能是返回满足条件的每组记录条数
count(*):返回表中满足条件的行记录数
count([distinct] column|expression):返回满足条件的非空行数量
使用distinct可以消除重复行,然后在count再计数:查询部门数量
select count(distinct deptno) from emp
空值处理:
除了count(*)之外,其它所有分组函数都会忽略列中的空值,然后再进行计算
经典案例:计算平均奖金(有的员工奖金为空)
select avg(nvl(comm, 0)) from emp;
创建数据组:group by 子句
可以通过group by子句将记录按照指定的列划分为若干个小组
查询每个部门的编号,平均工资
select depno, avg(sal) from emp group by deptno;
注意:在select列表中除了分组函数的列,其他所有的列都必须包含在group by中
group by所指定的列并不是必须出现在select列表中
也就是说:前面出现,后面必须出现,后面出现,前面不一定出现
可以按照多列分组:查询每个部门每个岗位的工资总和
select deptno, job, sum(sal) from emp group by deptno, job;
排除组结果,使用having子句限制组
注意:不能再where子句中限制组
使用having子句:记录已经分组,使用过组函数,与having子句匹配的结果才会输出
查询每个部门最高工资大于2900的部门编号,最高工资
select deptno, max(sal) from emp
group by deptno having max(sal)>2900;
组函数的嵌套
注意:与单行函数不同,组函数只能嵌套两层
显示部门最大平均工资
select max(avg(sal)) from emp group by deptno;
SELECT语句执行过程:
–1.通过FROM子句中找到需要查询的表;
–2.通过WHERE子句进行非分组函数筛选判断;
–3.通过GROUP BY子句完成分组操作;
–4.通过HAVING子句完成组函数筛选判断;
–5.通过SELECT子句选择显示的列或表达式及组函数;
–6.通过ORDER BY子句进行排序操作。
SELECT [column,] group_function(column)
FROM table
[WHERE condition]
[GROUP BY column]
[HAVING group_function(column)expression
[ORDER BY column| group_function(column)expression];
5个常用的分组函数
MIN
MAX
min和max可以用于任何数据类型
查询入职日期最早和最晚的日期
select min(hiredate), max(hiredate) from emp;
SUM
AVG
sum和avg函数只能够对数值类型的列或表达式操作
查询职位以SALES开头的所有员工的平均工资、最低工资、最高工资、工资和
select avg(sal),max(sal),min(sal),sum(sal) from emp
where job like 'SALES%';
COUNT
count函数的主要功能是返回满足条件的每组记录条数
count(*):返回表中满足条件的行记录数
count([distinct] column|expression):返回满足条件的非空行数量
使用distinct可以消除重复行,然后在count再计数:查询部门数量
select count(distinct deptno) from emp
空值处理:
除了count(*)之外,其它所有分组函数都会忽略列中的空值,然后再进行计算
经典案例:计算平均奖金(有的员工奖金为空)
select avg(nvl(comm, 0)) from emp;
创建数据组:group by 子句
可以通过group by子句将记录按照指定的列划分为若干个小组
查询每个部门的编号,平均工资
select depno, avg(sal) from emp group by deptno;
注意:在select列表中除了分组函数的列,其他所有的列都必须包含在group by中
group by所指定的列并不是必须出现在select列表中
也就是说:前面出现,后面必须出现,后面出现,前面不一定出现
可以按照多列分组:查询每个部门每个岗位的工资总和
select deptno, job, sum(sal) from emp group by deptno, job;
排除组结果,使用having子句限制组
注意:不能再where子句中限制组
使用having子句:记录已经分组,使用过组函数,与having子句匹配的结果才会输出
查询每个部门最高工资大于2900的部门编号,最高工资
select deptno, max(sal) from emp
group by deptno having max(sal)>2900;
组函数的嵌套
注意:与单行函数不同,组函数只能嵌套两层
显示部门最大平均工资
select max(avg(sal)) from emp group by deptno;
SELECT语句执行过程:
–1.通过FROM子句中找到需要查询的表;
–2.通过WHERE子句进行非分组函数筛选判断;
–3.通过GROUP BY子句完成分组操作;
–4.通过HAVING子句完成组函数筛选判断;
–5.通过SELECT子句选择显示的列或表达式及组函数;
–6.通过ORDER BY子句进行排序操作。