多行函数 聚集函数
常用聚集函数:是对一组或一批数据进行综合操作后返回一个结果
count 行总数
avg 平均数
sum 列值的和
max 最大值
min 最小值
count([{distinct|all} '列名'|*) 为列值时空不在统计之内,为*时包含空行和重复行
idle> select count(comm) from emp;
COUNT(COMM)
-----------
4
idle> select count(ename) from emp;
COUNT(ENAME)
------------
14
idle> select count(*) from emp;
COUNT(*)
----------
14
idle>
idle> select count(deptno) from emp;
COUNT(DEPTNO)
-------------
14
idle> select count(distinct deptno) from emp;
COUNT(DISTINCTDEPTNO)
---------------------
3
idle> select count(all deptno) from emp;
COUNT(ALLDEPTNO)
----------------
14
idle>
idle> select avg(sal),sum(sal),max(sal),min(sal),count(sal) from emp;
AVG(SAL) SUM(SAL) MAX(SAL) MIN(SAL) COUNT(SAL)
---------- ---------- ---------- ---------- ----------
2073.21429 29025 5000 800 14
idle>
上面执行的分组函数都是对所有记录统计,如果想分组统计(比如统计部门的平均值)需要使用group by 为了限制分组统计的结果需要使用having过滤
GROUP BY 分组统计 9I要排序 10G不排序
求出没个部门的平均工资
idle> select deptno,avg(sal) from emp group by deptno;
DEPTNO AVG(SAL)
---------- ----------
30 1566.66667
20 2175
10 2916.66667
idle>
分组再排序
idle> select deptno,avg(sal) from emp group by deptno order by deptno ;
DEPTNO AVG(SAL)
---------- ----------
10 2916.66667
20 2175
30 1566.66667
idle>
分组修饰列可以是未选择的列
idle> select avg(sal) from emp group by deptno order by deptno ;
AVG(SAL)
----------
2916.66667
2175
1566.66667
idle>
如果在查询中使用了分组函数,任何不在分组函数中的列或表达式必须在group by子句中
因为分组函数是返回一行 而其他列显示多行 显示结果矛盾.
idle> select avg(sal) from emp ;
AVG(SAL)
----------
2073.21429
idle> select deptno,avg(sal) from emp;
select deptno,avg(sal) from emp
*
ERROR at line 1:
ORA-00937: not a single-group group function
idle> select deptno,avg(sal) from emp group by deptno ;
DEPTNO AVG(SAL)
---------- ----------
30 1566.66667
20 2175
10 2916.66667
idle>
group by多条件分组
SCOTT@ora11g> select deptno,job,avg(sal),max(sal) from emp group by deptno,job order by 1;
DEPTNO JOB AVG(SAL) MAX(SAL)
---------- --------- ---------- ----------
10 CLERK 1300 1300
10 MANAGER 2450 2450
10 PRESIDENT 5000 5000
20 ANALYST 3000 3000
20 CLERK 950 1100
20 MANAGER 2975 2975
30 CLERK 950 950
30 MANAGER 2850 2850
30 SALESMAN 1400 1600
9 rows selected.
group by 的过滤
查出平均工资大于2000的部门
idle> select deptno,avg(sal) avg from emp group by deptno where avg >2000;
select deptno,avg(sal) avg from emp group by deptno where avg >2000
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
group by后不能再接where子句过滤 where过滤只能加到group by前端 这样又不能满足要求
对分组后的过滤要使用having
idle> select deptno,avg(sal) avg from emp group by deptno having avg(sal) >2000;
DEPTNO AVG
---------- ----------
20 2175
10 2916.66667
idle>
where与having一同使用
idle> select deptno,avg(sal) avg from emp where deptno != 10 group by deptno having avg(sal) >2000;
DEPTNO AVG
---------- ----------
20 2175
idle>
idle>
分组函数的嵌套
不嵌套效果
SCOTT@ora11g> select avg(sal),avg(sal) from emp where ename != 'KING' group by job;
AVG(SAL) AVG(SAL)
---------- ----------
1037.5 1037.5
1400 1400
2758.33333 2758.33333
3000 3000
SCOTT@ora11g>
求每个职位的最高平均工资和最低平均工资
idle> select min(avg(sal)),max(avg(sal)) from emp where ename != 'KING' group by job;
MIN(AVG(SAL)) MAX(AVG(SAL))
------------- -------------
1037.5 3000
idle>
求平均奖金 avg(comm) 即可 因为除了count(*)以外 其他分组函数都不处理空
idle> select avg(comm) from emp;
AVG(COMM)
----------
550
idle>
想求出所有员工的平均 需要使用nvl
idle> select avg(nvl(comm,0)) from emp;
AVG(NVL(COMM,0))
----------------
157.142857
idle>
分组函数的注意事项:
1.分组函数只能出现在选择列,order by,having子句中
2.分组函数会忽略NULL 除了count(*)
3.分组函数中可以使用ALL或distinct;ALL是默认值,统计所有.加上distinct则只统计不同
4.如果选择的列里有普通列,表达式和分组列 那么普通列和表达式都必须出现在group by中
//聚集函数嵌套,则不能显示分组列
SYS@orcl> select max(avg(sal)) from scott.emp group by deptno;
MAX(AVG(SAL))
-------------
2916.66667
行转列
create table t4(id int,name varchar2(10),subject varchar2(20),grade number);
insert into t4 values(1,'ZORRO','语文',70);
insert into t4 values(2,'ZORRO','数学',80);
insert into t4 values(3,'ZORRO','英语',75);
insert into t4 values(4,'SEKER','语文',65);
insert into t4 values(5,'SEKER','数学',75);
insert into t4 values(6,'SEKER','英语',60);
insert into t4 values(7,'BLUES','语文',60);
insert into t4 values(8,'BLUES','数学',90);
insert into t4 values(9,'PG','数学',80);
insert into t4 values(10,'PG','英语',90);
commit;
SQL> select * from t4;
ID NAME SUBOBJECT GRADE
---------- ---------- -------------------- ----------
1 ZORRO 语文 70
2 ZORRO 数学 80
3 ZORRO 英语 75
4 SEKER 语文 65
5 SEKER 数学 75
6 SEKER 英语 60
7 BLUES 语文 60
8 BLUES 数学 90
9 PG 数学 80
10 PG 英语 90
10 rows selected.
SQL>
----------------------------
SQL> select name,sum(case when SUBJECT='语文' then GRADE else 0 end) "语文",
sum(case when SUBOBJECT='数学' then GRADE else 0 end) "数学",
sum(case when SUBJECT='英语' then GRADE else 0 end) "英语" from t4 group by name;
select name,max(decode(subject,'语文',grade)) 语文,max(decode(subject,'数学',grade)) 数学,
max(decode(subject,'英语',grade)) 英语 from t4 group by name;
NAME 语文 数学 英语
---------- ---------- ---------- ----------
SEKER 65 75 60
BLUES 60 90 0
PG 0 80 90
ZORRO 70 80 75