Oracle基础(7)高级查询

分组查询

分组函数：作用于一组数据，并对一组数据返回一个值

常用的分组函数：AVG  SUM  MIN  MAX  COUNT  VM_CONCAT

最后一个是：行转列

SELECT AVG(sal),SUM(sal) FROM emp;

SELECT COUNT(*) FROM emp;

如果要求部门的个数

SELECT COUNT(distinct deptno) FROM emp; 

distinct函数是去重

VM_CONCAT：行转列

示例：SELECT deptno,wm_concat(ename) FROM emp GROUP BY deptno;

示例：SELECT deptno 部门号,wm_concat(ename) 部门中员工的姓名 FROM emp GROUP BY deptno;

set linesize 200

col 部门中员工的姓名 for a60

SELECT deptno 部门号,wm_concat(ename) 部门中员工的姓名 FROM emp GROUP BY deptno;

员工表中有3个部门，部门当中有多个员工

wm_concat()可以用   ，    分割

分值函数与空值

存在空值算平均奖金会出错

分组函数会自动忽略空值

可以使用NVL函数使分组函数无法忽略空值

NVL函数使ORACLE当中的允控函数，可以把空值转变成非空值

count(nvl(comm,0))

nvl函数就是当第一个参数为空则返回第二个参数，不为空就返回第一个参数

分组数据      GROUP BY

SELECT deptno,AVG(sal) FROM emp GROUP BY deptno;

我们把这套select语句抽象成

SELECT a,组函数(x)

FROM table

GROUP BY a;

deptno不能再AVG(sal)的组函数里面， a不能再x里面

如果 查的是a,b,c 其中a、b、c没有在组函数里面     ，在组函数只是x

group by 子句必须a，b，c 否则会语法错误

在SELECT列表中所有未包含在组函数中的e列都应该包含在GROUP BY子句中

包含在    GROUP BY子句中的列不必包含在SELECT列表中

例如SELECT AVG(sal) FROM emp GROUP BY deptno;

使用多个列分组

SELECT a,组函数(x)

FROM table

GROUP BY a;

SELECT a,组函数(x)

FROM table

GROUP BY a;

多个列分组：按照部门不同职位统计员工工资的总和

就需要按照部门的部门号和邀功的职位两个列进行分组

SELECT deptno,job,sum(sal)

FROM emp

GROUP BY deptno,job;

SELECT deptno,job,sum(sal)

FROM emp

GROUP BY deptno,job ORDER BY deptno;

非法使用组函数

所有包含SELECT列表中，而未包含于组函数中的列都必须包含于GROUP BY子句中

过滤分组  使用having子句

分组函数也叫组函数也叫多行函数

   SELECT deptno,avg(sal) FROM emp GROUP BY deptno HAVING AVG(sal)>2000;

使用HAVING过滤分组的数据

注意：    不能再WHERE子句中使用组函数

可以再HAVING子句中使用组函数

WHERE和HAVING都是过滤结果

例子：查询10号部门的平均工资

这个问题即可以使用WHERE也可以使用HAVING

从SQL优化的角度上看，尽量使用WHERE

HAING  分组的基础之上过滤分组的结果   也就是说先分组再过滤

WHERE   是先过滤再分组

比如员工用1亿个员工，10号部门只有100个  如果使用HAVING先分组就是1亿条

使用WHERE只会分组100个

WHERE可以大量提高效率

不过WHERE子句中不能使用分组函数

在分组查询中使用OREDER BY子句