oracle分组函数优化,oracle分组函数笔记

分组统计查询

分组函数展示

count(*|[distinct]列)：求出全部记录数

如果列上有null值则不统计，distinct重复记录也不统计。建议用count(字段)，必须用一个不可能为空字段。

sum：求出总和，操作列为数字

avg：平均值

max：最大值

min：最小值

median：返回中间值

variance：返回方差

stddev：返回标准差

注：所有的统计函数中只有count函数可以在表中没有任何记录的时候返回结果。

单字段分组统计操作

引入：正常情况下都允许重复数据分组，实际上也允许一条数据一组。

group by 子句

select子句中允许分组字段，统计函数

注：分组操作的若干限制

注意事项一：

如果在一个查询中不存在group by 子句，那么在select子句之中只允许出现统计函数，其他的任何字段都不允许出现。

注意事项二：

在统计查询中，select 子句之中只允许出现分组字段和统计函数。

注意事项三：

统计函数允许嵌套使用，但是一旦使用了嵌套的统计函数之后，select子句之中不允许出现任何字段，包括分组字段。

例如：求出每个部门平均工资最高的工资

select max(avg(sal)) from emp group by deptno;

步骤：

多表查询数据统计，临时表分组，行列的组成结果一定是临时表。

先确定表

确定关联字段

根据规则进行分组统计

例如：

统计出领取佣金与不领取佣金的雇员的平均工资、平均雇佣年限、雇员人数。

SELECT '领取佣金', ROUND(AVG(sal),2) avgsal,

ROUND(AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/12),2) avgyear,

COUNT(empno) count

FROM emp

WHERE comm IS NOT NULL ;

SELECT '领取佣金', ROUND(AVG(sal),2) avgsal,

ROUND(AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/12),2) avgyear,

COUNT(empno) count

FROM emp

WHERE comm IS NULL ;

SELECT '不领取佣金', ROUND(AVG(sal),2) avgsal,

ROUND(AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/12),2) avgyear,

COUNT(empno) count

FROM emp

WHERE comm IS NOT NULL

UNION

SELECT '领取佣金', ROUND(AVG(sal),2) avgsal,

ROUND(AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/12),2) avgyear,

COUNT(empno) count

FROM emp

WHERE comm IS NULL ;

多字段分组

注：多字段分组是group by 定义的多个字段，且多个字段必须同时重复。

having子句

与group子句进行配合使用，对分组后的数据进行统计过滤，因为where在group by 之前执行，所以where中不允许使用统计函数，所以只能利用having子句。

步骤

1.关联表查出基本数据

2.临时表分组

执行顺序

from->where->group by->having->select->order by

where与having区分

where：在分组之前使用(可以没有group by)，不允许使用统计函数

having：是在分组之后时候(必须有group by)，允许使用统计函数

例子：

显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于$5000，输出结果按月工资的合计升序排列

步骤一

SELECT *

FROM emp

WHERE job<>'SALESMAN' ;

步骤二

SELECT job , SUM(sal) sum

FROM emp

WHERE job<>'SALESMAN'

GROUP BY job ;

步骤三、

SELECT job , SUM(sal) sum

FROM emp

WHERE job<>'SALESMAN'

GROUP BY job

HAVING SUM(sal)>5000 ;

步骤四、

SELECT job , SUM(sal) sum

FROM emp

WHERE job<>'SALESMAN'

GROUP BY job

HAVING SUM(sal)>5000

ORDER BY sum ASC ;

特别注意：由于order by 最后执行，所以可以使用select里的别名。

总结

戒骄戒躁