分组函数(多行函数)
分组函数作用于一组数据,并对一组数据返回一个值。
–max min avg count sum
select max(salary),min (salary), avg(salary),sum(salary),count(salary)–计数
from employees;
group by 分组
- 出现在select子句后的非分组函数,必须出现在group by 子句后
-- 当前各个部门的平均工资
select department_id, avg(salary)
from employees
group by department_id;
-- 求各个部门中每个工种(job_id)的平均工资
select department_id, job_id, avg(salary)
from employees
group by department_id, job_id;
- where 不能过滤组函数,如果需要过滤组函数,使用关键字having
select department_id. avg(salary)
from employees
group by department_id
having avg(salary)>8000;
-mysql中组函数不能嵌套,但是在oracle数据库中可以嵌套,但是不能嵌套太深
使用子查询解决需求子函数嵌套问题,但是表得有别名
select max(avg_sal)
from (
select avg(salary) avg_sal
from employees
group by department_id
) e;