Oracle复习之三组函数

组函数 把多条记录作为输入返回一个输出
select max(sal) from emp;// MAX(SAL) 5000
select min(sal) from emp;
select avg(sal) from emp;
select to_char(avg(sal),'99999999.99') from emp;// 2073.21
select round(avg(sal),1) from emp;//2073.2
select sum(sal) from emp;
select count(*) from emp;//计数有多少条记录
select count(*) from emp wehre deptno=10;
select count(comm) from emp;//count是查有值没有
select count(distinct deptno) from emp;查寻有多少部门编号

分组group by 如果用组函数avg max等，select不是组函数的字段必须按该字段分组
select deptno,avg(sal) from group by deptno;
select deptno,job，max(sal) from emp group by deptno,job;按2个分组
select ename,max(sal) from emp;//错误不是单组分组函数
select ename from emp where sal= (select max(sal) from emp);

having语句对分组进行限制，where是对单条语句过滤
select avg(sal),deptno from emp group by deptno;
//查按deptno分组后sal平均值大于2000的，不能用where语句
select avg(sal),deptno from emp group by deptno having avg(sal) >2000;

SQL顺序
select * from emp where sal>1000 group by deptno having avg(sal)>2000 order by ....

求薪水大于1200的雇员 按照部门编号进行分组，分组后平均薪水大于1500，
查询分组内的平均工资，按照平均薪水倒叙进行排列
select deptno,avg(sal)
from emp
where sal>1200
group by deptno
having(avg(sal)>1500)
order by avg(sal) desc;