sql 分组

AVG(),
select avg(comm) from emp;
select t.deptno, avg(sal) from emp t group by  t.deptno


SUM()
select t.deptno,sum(sal) from emp t group by  t.deptno


MIN()


select t.deptno,sum(sal+nvl(comm,0)) from emp t group by  t.deptno


MAX()
-------分组函数 不能跟单行数据一起用


select max(sal) from emp t where t.deptno=10


-------where 后面不能用分组函数


select ename from emp t  where  max(sal)>500 and  t.deptno=10




COUNT()
--------


select count(ename) from emp;


select count(*) from emp;




--------分组函数嵌套使用




select  max(avg(sal)) from emp t group by  t.deptno




-----分组关键字


---group by


select max(sal)-----3
 
from  emp------1


group by deptno-----2
 
-----order by


select max(sal)-----3
 
from  emp------1


group by deptno-----2


order by max(sal)-----4


----having 


select max(sal)-----4
 
from  emp------1


group by deptno-----2


having max(sal)>2850----3


order by max(sal)-----5




---------分组函数执行顺序


select max(sal)-----5
 
from  emp------1


where  deptno in(10,20)-----2


group by deptno-----3


having max(sal)>2850----4


order by max(sal)-----6