分组函数:通过使用组函数,利用分组,对一组数据返回一个值
组函数类型:
select avg(salary) “平均值”,sum(salary) “求和”,min(salary) “最小值”,
max(salary) “最大值”,count(*)“总条数”
from employees
where department_id in (100,110);
图示1.1:
注:avg函数和sum函数只能对数值型数据进行使用,min函数、max函数和count函数适用于任何数据类型,where条件中不能使用分组函数
组函数忽略空值,要用NVL函数对空值进行处理
select count(commission_pct),count(nvl(commission_pct,0))
from employees;
图示1.2:
Distinct关键字:去重复
select department_id,count(distinct department_id),
count(distinct nvl(department_id))
from employees
group by department_id;
图示1.3:
分组数据:
group by子句:在select列表中所有未包括在组函数中的字段都要写入到group by子句中
select department_id,last_name,avg(salary)
from employees
group by department_id,last_name;
图示1.4:
having子句:虽然where子句中不能使用组函数,但在having子句中可以使用
如:查询出各个部门平均工资大于7000的平均工资
select department_id,max(salary)
from employees
group by department_id
having avg(salary)>7000
order by department_id;–排序
图示1.5:
嵌套组函数:
如:查询各个部门最高工资中的最低工资
select min(max(salary))
from employees
group by department_id;
图1.6:
子查询中也会用到组函数
如:查询工资在6000和平均工资之间的员工信息
select employee_id,last_name,salary
from employees
group by employee_id,last_name,salary
having avg(salary) between 6000 and (select avg(salary) from employees);
图1.7: