ORACLE SQL 组函数 （聚合函数）

爱她吗

　　第五章:组函数
　　组函数（多行函数）默认情况下组函数忽略null
　　select min(sal),max(sal),sum(sal),avg(sal) from emp;
　　select min(hiredate),max(hiredate) from emp;
　　select min(ename),max(ename) from emp;
　　select count(*) from emp;
　　select count(*) from emp where deptno=30;
　　select count(comm) from emp;
　　select count(distinct deptno) from emp;
　　select avg(distinct sal) from emp;
　　select avg(nvl(comm,0)) from emp;
　　分组计算(group by):
　　select sum(sal) from emp group by deptno;
　　select deptno,sum(sal) from emp group by deptno;
　　--error
　　select deptno,sum(sal) from emp;
　　select deptno,job,sum(sal) from emp group by deptno,job order by 1;
　　过滤组函数运算后的结果(having):
　　select deptno,avg(sal) from emp
　　having avg(sal)>2000
　　group by deptno;
　　重复的工资值:
　　select sal,count(sal) from emp
　　having count(sal)>1
　　group by sal;
　　每年参加工作的雇员有多少人？
　　select to_char(hiredate,'yyyy') year,count(*) total
　　from emp
　　group by to_char(hiredate,'yyyy');
　　--行列转换
　　create table test (a int,b varchar2(1),c varchar2(7));
　　insert into test values (1,'a','BUMEN');
　　insert into test values (1,'b','BIANHAO');
　　insert into test values (1,'c','ZHIWEI');
　　insert into test values (2,'d','BUMEN');
　　insert into test values (2,'e','BIANHAO');
　　insert into test values (2,'f','ZHIWEI');
　　commit;
　　--打印如下结果集
　　A BUMEN BIANHAO ZHIWEI
　　----- --------- -------- --------
　　1 a b c
　　2 d e f
　　col BUMEN for a7
　　col BIANHAO for a7
　　col ZHIWEI for a7
　　select a,
　　max(decode(c,'BUMEN',b)) BUMEN,
　　max(decode(c,'BIANHAO',b)) BIANHAO,
　　max(decode(c,'ZHIWEI',b)) ZHIWEI
　　from test
　　group by a;