Oracle数据库基础SQL语句(三)—高级查询（分组查询）

Oracle的高级查询之分组查询

• 常用的分组函数

注：创建Oracle数据库自带SCOTT,里边有表EMP…

1. – AVG 平均值 SUM(expr)求和

• 求出员工的平均工资和工资的总额

 SELECT AVG(SAL) as 平均工资,SUM(SAL) as 总 from EMP;

2. – min（最小值）max()

• 求出员工工资的最大值和最小值

SELECT MIN(SAL),max(SAL)from EMP;

3. – COUNT(expr):计数函数

• 求出员工的总人数

SELECT COUNT(SAL)from EMP;
DISTINCT(去掉重复的记录)
SELECT DISTINCT COUNT(SAL) from EMP;//这样显示的依然是14
SELECT COUNT(DISTINCT SAL)from EMP;//正确写法

4. – WM_CONCAT:行转列

SELECT DEPTNO as 部门号,wm_concat(ENAME)as 部门中的员工姓名 FROM EMP GROUP BY DEPTNO;

分组函数与空值(分组函数自动忽略空值)

1. 统计员工的平均工资(三种方法)

注：此处as进行了省略

SELECT AVG(SAL) 一,SUM(SAL)/COUNT(SAL) 二,SUM(SAL)/COUNT(*) 三 FROM EMP;

因为此处count（*）=15

2. 统计员工的平均奖金（奖金含有空值）

SELECT AVG(COMM) 一,SUM(COMM)/COUNT(COMM) 二,SUM(COMM)/COUNT(*) 三 FROM EMP;

3. 在分组函数中使用NVL函数（NVL函数使用分组函数时无法忽略空值）

注：nvl的两个参数的意思是，当第一个参数不为空时，返回第一个参数，否则返回第二个参数

SELECT COUNT(*)，COUNT(NVL(COMM, 0))FROM EMP;

GROUP BY 语句

（可以将表中的数据分成若干组）简单来讲就是by谁就按着谁分组

1. 求每个部门的平均工资，要求显示部门号，部门平均工资

SELECT DEPTNO,AVG(SAL)from EMP GROUP BY DEPTNO;

注：在select列表中所有未包含在组函数的列，都应该包含在GROUP BY字句中,否则会语法错误

SELECT a,b,c,组函数（x）
from EMP
GROUP BY a,b,c;

2. 使用多个列分组

• 按照部门，不同的职位，统计员工的工资总额

SELECT DEPTNO,JOB,AVG(SAL)from EMP GROUP BY DEPTNO,JOB;
//看起来换位置是没有问题的，都写上就可以
SELECT DEPTNO,JOB,AVG(SAL)from EMP GROUP BY JOB,DEPTNO;
//进行排序
SELECT DEPTNO,JOB,AVG(SAL)from EMP GROUP BY JOB,DEPTNO ORDER BY DEPTNO;

– 注：oracle的错误都是ORA打头的

过滤分组 HAVING字句的使用（跟where有点类似）

1. 求平均工资大于2000的部门

SELECT DEPTNO,AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING AVG(SAL)<2000; 

由上条语句看出where和having的区别：where不能包含组函数，having可以；
但是从SQL优化的角度上来说，尽量使用where（因为having是先分组在过滤，where是先过滤在分组，大大降低了分组记录数，提高效率）

在分组查询中使用order by子句

求每个部门的平均工资，要求显示：部门号，部门平均工资，并按照工资升序排列（可以按照：列，别名，表达式，序号进行排列）

SELECT DEPTNO,AVG(SAL) 
from EMP
GROUP BY DEPTNO
ORDER BY AVG(SAL);//按照列排序，（默认就是升序）

SELECT DEPTNO,AVG(SAL) as 平均工资 //当函数复杂时可以起别名，不过这好像起别名以后复杂了 

from EMP
GROUP BY DEPTNO
ORDER BY 2;  //此处的2代替平均工资，因为这列是查询主句的第2列

降序排列

SELECT DEPTNO,AVG(SAL) as 平均工资
from EMP
GROUP BY DEPTNO
ORDER BY 2 DESC;

分组函数的嵌套

求部门工资的最大值 avg函数嵌套max函数

SELECT MAX(AVG(SAL)) FROM EMP GROUP BY DEPTNO;

GROUP BY语句的增强(适合做报表)

GROUP BY rollup(a,b)等价于：GROUP BY a,b+GROUP BY a+GROUP BY NULL(没有条件)

SELECT DEPTNO,JOB,SUM(SAL)from EMP GROUP BY DEPTNO,JOB; //红色方框
+
SELECT DEPTNO,JOB,SUM(SAL)from EMP GROUP BY DEPTNO; //蓝色方框
+
SELECT DEPTNO,JOB,SUM(SAL)from EMP; //紫色方框
 ===
SELECT DEPTNO,JOB,SUM(SAL)from EMP GROUP BY rollup(DEPTNO,JOB);

进行报表格式的调整？