08-Oracle入门之分组函数
基本概念分组函数作用于一组数据,并对一组数据返回一个值。
SELECT [column,] group_function(column),... FROM table [WHERE condition] [GROUP BY column] [ORDER BY column];
AVG(平均值)和 SUM (合计)函数可以对数值型数据使用AVG 和 SUM 函数。 SELECT AVG(salary),MAX(salary),MIN(salary),SUM(salary) FROM employees WHERE job_id LIKE '%REP%';
求员工的平均comm 奖金 错误写法: SQL> select avg(comm) AA,sum(comm)/count(empno) BB,sum(comm)/count(comm) CC from emp;
AA BB CC
---------- ---------- ----------
550 146.666667 550
造成这个问题的原因:奖金含有空值,组函数能自动滤空,可以证明如下: SQL> select count(empno),count(comm),count(*) from emp; COUNT(EMPNO) COUNT(COMM) COUNT(*)
------------ ----------- ----------
15 4 15
屏蔽组函数的自动滤空功能–正确写法: SQL> select avg(comm) AA,sum(comm)/count(nvl(comm,0)) CC from emp;
AA BB CC
---------- ---------- ----------
550 146.666667 146.666667
MIN(最小值) 和 MAX(最大值)函数可以对任意数据类型的数据使用 MIN 和 MAX 函数。 SELECT MIN(hire_date),MAX(hire_date) FROM employees;
COUNT(计数)函数COUNT(*) 返回表中记录总数。 SELECT COUNT(*) FROM employees WHERE department_id = 50;
COUNT(expr) 返回 expr不为空的记录总数 SELECT COUNT(commission_pct) FROM employees WHERE department_id = 80;
DISTINCT(distinct) 关键字 COUNT(DISTINCT expr) 返回 expr非空且不重复的记录总数 SELECT COUNT(DISTINCT department_id) FROM employees;
组函数与空值组函数忽略空值。 SELECT AVG(commission_pct) FROM employees;
在组函数中使用NVL函数NVL函数使分组函数无法忽略空值。 SELECT AVG(NVL(commission_pct,0)) FROM employees;
GROUP BY 子句常见的分组数据场景: 可以使用GROUP BY 子句将表中的数据分成若干组。
SELECT column,group_function(column) FROM table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column];
SELECT department_id,AVG(salary) FROM employees GROUP BY department_id ;
SELECT AVG(salary) FROM employees GROUP BY department_id ;
在GROUP BY 子句中包含多个列 SELECT department_id dept_id,job_id,SUM(salary) FROM employees GROUP BY department_id,job_id ;
非法使用组函数所用包含于SELECT 列表中,而未包含于组函数中的列都 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
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
过滤分组:HAVING 子句使用 HAVING 过滤分组: SELECT column,group_function FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column];
应用: SELECT department_id,MAX(salary) FROM employees GROUP BY department_id HAVING MAX(salary)>10000 ;
having 和 where子句求10号部门的平均工资 1 先分组,再在having过滤–having在group by之后 select avg(sal) from emp group by deptno having deptno=10
2 先where过滤,在分组–where在group by之前 select avg(sal) from emp where deptno = 10 group by deptno
嵌套组函数显示平均工资的最大值 SELECT MAX(AVG(salary)) FROM employees GROUP BY department_id; (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |