基本概念
@H_502_3@分组函数作用于一组数据，并对一组数据返回一个值。@H_404_4@SELECT [column,] group_function(column),... FROM table [WHERE condition] [GROUP BY column] [ORDER BY column];
AVG（平均值）和 SUM （合计）函数
@H_502_3@可以对数值型数据使用AVG 和 SUM 函数。@H_404_4@SELECT AVG(salary),MAX(salary),MIN(salary),SUM(salary) FROM employees WHERE job_id LIKE '%REP%';
@H_502_3@求员工的平均comm 奖金@H_404_4@
@H_502_3@错误写法：@H_404_4@
sql> select avg(comm) AA,sum(comm)/count(empno) BB,sum(comm)/count(comm) CC from emp;
AA BB CC
---------- ---------- ----------
550 146.666667 550
@H_502_3@造成这个问题的原因:奖金含有空值,组函数能自动滤空,可以证明如下：@H_404_4@
sql> select count(empno),count(comm),count(*) from emp; COUNT(EMPNO) COUNT(COMM) COUNT(*)
------------ ----------- ----------
15 4 15
@H_502_3@屏蔽组函数的自动滤空功能–正确写法：@H_404_4@
sql> select avg(comm) AA,sum(comm)/count(nvl(comm,0)) CC from emp;
AA BB CC
---------- ---------- ----------
550 146.666667 146.666667
MIN（最小值） 和 MAX（最大值）函数
@H_502_3@可以对任意数据类型的数据使用 MIN 和 MAX 函数。@H_404_4@SELECT MIN(hire_date),MAX(hire_date) FROM employees;
COUNT（计数）函数
@H_502_3@COUNT(*) 返回表中记录总数。@H_404_4@SELECT COUNT(*) FROM employees WHERE department_id = 50;
@H_502_3@COUNT(expr) 返回 expr不为空的记录总数@H_404_4@
SELECT COUNT(commission_pct) FROM employees WHERE department_id = 80;
@H_502_3@DISTINCT（distinct） 关键字@H_404_4@
@H_502_3@COUNT(DISTINCT expr) 返回 expr非空且不重复的记录总数@H_404_4@
SELECT COUNT(DISTINCT department_id) FROM employees;
组函数与空值
@H_502_3@组函数忽略空值。@H_404_4@SELECT AVG(commission_pct) FROM employees;
在组函数中使用NVL函数
@H_502_3@NVL函数使分组函数无法忽略空值。@H_404_4@SELECT AVG(NVL(commission_pct,0)) FROM employees;
GROUP BY 子句
@H_502_3@常见的分组数据场景：@H_404_4@ @H_502_3@可以使用GROUP BY 子句将表中的数据分成若干组。@H_404_4@- 一般套路：
SELECT column,group_function(column) FROM table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column];
- 在SELECT 列表中所有未包含在组函数中的列都应该包含
在 GROUP BY 子句中。
SELECT department_id,AVG(salary) FROM employees GROUP BY department_id ;
@H_502_3@在group by 中没有出现的项,不能在select 进行检索@H_404_4@
- 包含在 GROUP BY 子句中的列不必包含在SELECT 列表中
SELECT AVG(salary) FROM employees GROUP BY department_id ;
- 使用多个列分组
SELECT department_id dept_id,job_id,SUM(salary) FROM employees GROUP BY department_id,job_id ;
非法使用组函数
@H_502_3@所用包含于SELECT 列表中，而未包含于组函数中的列都必须包含于 GROUP BY 子句中。@H_404_4@
SELECT department_id,COUNT(last_name) FROM employees;
SELECT department_id,COUNT(last_name)
*
ERROR at line 1:
ORA-00937: not a single-group group function
@H_502_3@GROUP BY 子句中缺少列@H_404_4@
SELECT department_id,AVG(salary)
FROM employees
WHERE AVG(salary) > 8000
GROUP BY department_id;
=====
WHERE AVG(salary) > 8000
*
ERROR at line 3:
ORA-00934: group function is not allowed here
@H_502_3@WHERE 子句中不能使用组函数@H_404_4@
过滤分组:HAVING 子句
@H_502_3@使用 HAVING 过滤分组:1. 行已经被分组。
2. 使用了组函数。
3. 满足HAVING 子句中条件的分组将被显示。
4. 一般套路@H_404_4@
SELECT column,group_function FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column];
@H_502_3@应用：@H_404_4@
SELECT department_id,MAX(salary) FROM employees GROUP BY department_id HAVING MAX(salary)>10000 ;
having 和 where子句
@H_502_3@求10号部门的平均工资@H_404_4@ @H_502_3@1 先分组,再在having过滤–having在group by之后@H_404_4@select avg(sal) from emp group by deptno having deptno=10
@H_502_3@2 先where过滤,在分组–where在group by之前@H_404_4@
select avg(sal) from emp where deptno = 10 group by deptno
嵌套组函数
@H_502_3@显示平均工资的最大值@H_404_4@SELECT MAX(AVG(salary)) FROM employees GROUP BY department_id;
