第二章 Oracle的 函数（聚合函数，分析函数）

聚合函数：
名称 作用 语法
avg 平均值 AVG(表达式)
sum 求和 SUM(表达式)
min\max 最小值、最大值 MIN(表达式)、MAX(表达式)
count 数据统计 COUNT(不为空的字段)
group by 分组
having 分组后的筛选
order by 一个或多个字段的 排序 默认升序排列 ASC，
DESC降序
null是最大值
SQL语句执行顺序：
select5 –from1–where2–group by3–having4–order by 6–

===============================================================
SELECT MAX(SAL),min(sal),
sum(sal),avg(sal),
count(1)
FROM EMP2;

SELECT e.deptno,max(d.dname),MAX(e.SAL),min(e.sal),
sum(e.sal),avg(e.sal),
count(1)
FROM EMP2 e ,dept d
where e.deptno = d.deptno
group by e.deptno

–having
select * from (
SELECT e.deptno,MAX(e.SAL) max_sal,min(e.sal) min_sal,
sum(e.sal) sum_sal,avg(e.sal) avg_sal,
count(1) as total
FROM EMP2 e
group by e.deptno ) t
where avg_sal >3000;

SELECT e.deptno,MAX(e.SAL) max_sal,min(e.sal) min_sal,
sum(e.sal) sum_sal,avg(e.sal) avg_sal,
count(1) as total
FROM EMP2 e
group by e.deptno having avg(e.sal)>3000

–Where可以筛选的条件，不要写在having后进行筛选
select deptno,sum(sal) from emp2 e
group by deptno having deptno in(20,30);
select deptno,sum(sal) from emp2 e
where deptno in(20,30)
group by deptno ;

===========================================================&#