oracle 中组函数,oracle中组函数

组函数的使用(组函数最多只能嵌套两次，并且只有在group by之后再能嵌套两次，如果没有group by，则只能嵌套一次)

1.特点：每组返回一个结果集。

avg:求平均值，会忽略null值。

count:求总个数，只有count(*)会包括null值，其他都忽略null值

max:求最大值，会忽略null值。

min:求最小值，会忽略null值。

sum:求总数，会忽略null值。

max，min ：求最大和最小值。

max和min里面可以是数字类型，字符类型和日期类型

SQL> select max(sal),min(sal),max(ename),min(ename) from emp;

MAX(SAL)   MIN(SAL) MAX(ENAME) MIN(ENAME)

---------- ---------- ---------- ----------

5000        800 WARD       ADAMS

这里的字符类型比较的是它的ASCII值。

AVG,sum,variance,stddev 这些函数只能是数字类型的。

count函数的使用：

返回的是一个列中的行数。

count函数的三种形式：

count(*):返回行数，包含相同值，null值。

SQL> select count(*) total_rows from emp;

TOTAL_ROWS

----------

14

count(expr)：返回不含null值的总和，会忽略null值。

SQL> select count(comm) from emp;

COUNT(COMM)

-----------

4

count(distinct expr)：返回不含null值和不相同的值的总和，会忽略null值。

SQL> select count(distinct sal) from emp;

COUNT(DISTINCTSAL)

------------------

12

在这几个函数中除了count(*)不会会忽略null值。

其他的函数都会忽略null，因为如果不忽略那么在计算的时候

时候，就会产生null值。null与任何数计算都为null。

但是在计算的时候，有时候需要那些没有值的列。

比如：计算所有员工的平均奖金。

SQL> select avg(comm) from emp;

AVG(COMM)

----------

550

SQL> select count(comm) from emp;

COUNT(COMM)

-----------

4

SQL> select sum(comm) from emp;

SUM(COMM)

----------

2200

这里只是计算四个人的。题目需要计算所有人员工。

这里我们需要有NVL函数来处理null值。

SQL> select avg(nvl(comm,0)) from emp;

AVG(NVL(COMM,0))

----------------

157.142857

group by：

如果select子句中的字段没有在聚合函数中，那么就必须出现在

group by 子句中。而出现在group by子句中不一定要出现在select子句中。

SQL> select deptno , avg(sal) from emp;

select deptno , avg(sal) from emp

*

ERROR at line 1:

ORA-00937: not a single-group group function

正确写法：

SQL> select deptno , avg(sal) from emp

2  group by deptno;

DEPTNO   AVG(SAL)

---------- ----------

30 1566.66667

20       2175

10 2916.66667

注意：组函数不能够用在where子句后面。(因为sql语句的执行顺序是先执行from子句，在执行where子句，在执行group by子句，在执行聚合函数，在执行having子句，在执行select，最后执行order by，如果在where子句中使用组函数，由于还没有执行group by 子句，所以不知道如何分组，不能计算avg(sal)的值，导致报错)

SQL> select deptno , avg(sal) from emp

2  where avg(sal)>2000;

where avg(sal)>2000

*

ERROR at line 2:

ORA-00934: group function is not allowed here

我们可以使用having来过滤分组过后的数据。

SQL> select deptno , avg(sal) from emp

2  group by deptno

3  having avg(sal)>2000;

DEPTNO   AVG(SAL)

---------- ----------

20       2175

10 2916.66667

having是过滤分组过后的数据，在having组函数不能用列别名。(由于sql语句的执行顺序，select语句的执行顺序比having要晚，所以不能够识别列别名，导致报错)(根据sql语句的执行顺序可知，只有order by的执行顺序比select晚，所以只有order by才可以使用列别名)

select deptno , avg(sal)avg_sal from emp

group by deptno

having avg_sal>2000

SQL> /

having avg_sal>2000

*

ERROR at line 3:

ORA-00904: "AVG_SAL": invalid identifier

having子句也可以用在group By 的前面，但是建议用在group by 的后面，以便于

逻辑理解。

having 和 where的区别：

1.having只能用在group by 后面。有having肯定有group by子句。

2.where子句用在having前面。

3.having 是用于分组统计过后的过滤，而where是过滤的是原始数据，也就是分组统计之前的数据。

having后面最好只限定组函数，如：

求出employees表中部门号小于90的最大工资大于10000的工资

1.select department_id ,max(salary) from employees where department_id<90 group by department_id having max(salary)>10000;

2.select department_id ,max(salary) from employees  group by department_id having max(salary)>10000 and department_id<90;

1和2语句虽然最后得出的结果都正确，但是2语句比1语句中执行了更多的max()(因为2语句对department_id<90的也惊醒max操作)，会对执行效率产生影响

当使用GROUP BY子句时Orale服务器会自动对结果集合默认按GROUP BY子句所指定的列升序排列

按多个列分组

例：

SELECT   deptno,job,sum(sal)

FROM     emp

GROUP BY deptno,job;

oracle会先对deptno分组，当deptno相同时，在对job分组