ORACLE基础 ——分组统计

ericjjc

1   组函数

所谓分组统计，即先分组，再统计。单纯的分组没有啥意思，我们关注的是分出来的各组的各种属性。比如我去买鞋子，看到百货商店里的鞋子分为“匡威”“耐克”“阿迪”和“内联升”等4组，这些组本身对我们并不重要，对我们重要的是哪一组鞋最耐穿，哪一组的款式更适合我们。如果您想买双布鞋，您会去耐克专卖吗？（咦？我怎么扯了这么多废话，咱们挨踢er们最不喜欢的就是废话，我还是赶紧切入正题吧，小弟下边再不说废话了。。。^^）

相应的，数据库中的分组，也需要一些函数来求出这些组的某些属性，比如：

COUNT():  求出全部的记录数；

MAX():      求出一组中的最大值；

MIN():       求出一组中的最小值；

AVG():       求出一组中的平均值；

SUM():      求和；

这些函数的实际效果就不举例子了，比较简单，大家自己试一试就行了，咱们还是把注意力放到分组统计上来。

2   分组统计

先大致看一下语法格式，知道长什么模样就行，不用去背，枯燥无味，没啥意思。

    SELECT ***

    FROM ***

    (WHERE 条件)

    (GROUP BY 分组条件)

    (ORDER BY 排序字段 ASC|DESC) ；

P.S. 用“()”括起来的表示可有可无。

2.1示例用表

      以下示例均以Oracle 10g 自带的emp（雇员表）为基础。

Emp表

2.2普通情况

范例：求出每个部门的雇员数量，应该按照部门编号进行分组

SELECT deptno ,COUNT(empno)

FROM emp

GROUP BY deptno

;

或者加上排序：

SELECT deptno ,COUNT(empno)

FROM emp

GROUP BY deptno

ORDER BY COUNT(EMPNO) DESC

;

2.3错误1

SELECT deptno, COUNT(empno)

FROM emp

;

这样就引入了规则一：

如果程序中使用了分组函数，则有两种可以使用的情况

1）  如果要查询(SELECT)某字段和分组函数，则必须使用GROUP BY来制定分组条件；

2）  单独SELECT分组函数，不加某个字段，这样就不用使用GROUP BY;

1)       效果如2.2普通情况

2)       效果如下：

SELECT count(deptno)         //只有分组函数哦！

FROM emp ;

2.4错误2

SELECT t.mgr, t.deptno, count(*) cnt

FROM emp t

GROUP BY t.mgr

ORDER BY cnt

;

所以，规则二：

在使用分组函数的时候，不能出现分组函数和分组条件之外的字段；

解决办法是：查询多少字段，就GROUP BY多少字段

上边范例可改为：

SELECT t.mgr, t.deptno, count(*) cnt

FROM emp t

GROUP BY t.mgr, t.deptno

ORDER BY cnt

;

2.5错误3

要求显示出平均工资大于2000的部门编号和平均工资

SELECT deptno,AVG(sal)

FROM emp

WHERE AVG(sal)>2000

GROUP BY deptno ;

所以，规则三：

分组函数只能在分组中使用，不允许在WHERE子句中出现，

如果要指定分组的条件，则只能通过第二种条件指令：HAVING;

上边范例可修改为：

SELECT t.deptno, AVG(sal)

FROM emp t

GROUP BY t.deptno

HAVING AVG(sal) > 2000 ;

2.6错误4

分组函数的套嵌使用

范例：求出平均工资 最高的部门工资；

SELECT deptno ,MAX(AVG(sal))

FROM emp

GROUP BY deptno ;

所以，规则4：

分组函数可以嵌套使用，但是此时不能再出现分组条件的查询语句(即不能SELECT要分组的字段)；

方法一，去掉deptno：

SELECT MAX(AVG(sal))

FROM emp

GROUP BY deptno ;

但是这样就不知道是哪个部门了。。。

方法二，把想要查询的字段也写成函数：

SELECT MAX(deptno) ,MAX(AVG(sal))

FROM emp

GROUP BY deptno ;

3   结束语

         好了，常用的分组查询就是这些情况，简单做一下总结：

规则一：如果程序中使用了分组函数，则有两种可以使用的情况

3）  如果要查询(SELECT)某字段和分组函数，则必须使用GROUP BY来制定分组条件；

4）  单独SELECT分组函数，不加某个字段，这样就不用使用GROUP BY;

 

规则二：在使用分组函数的时候，不能出现分组函数和分组条件之外的字段；

解决办法是：查询多少字段，就GROUP BY多少字段

 

规则三：分组函数只能在分组中使用，不允许在WHERE子句中出现，

如果要指定分组的条件，则只能通过第二种条件指令：HAVING;

 

规则四：分组函数可以嵌套使用，但是此时不能再出现分组条件的查询语句(即不能SELECT要分组的字段)；

P.S. 参考资料：MLDN Oracle资料