第五章:组函数
组函数（多行函数）默认情况下组函数忽略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;
