统计查询

来源：互联网发布：蚁群算法两点最短路径编辑：程序博客网时间：2024/04/29 22:15

转自： http://www.cnblogs.com/junyuz/archive/2011/03/14/1983364.html

统计查询
通常需要对数据进行统计，汇总出数据库的统计信息。比如，我们可能想了解公司的总人数和总工资额，或各个部门的人数和工资额，这个功能可以由统计查询完成。
Oracle提供了一些函数来完成统计工作，这些函数称为组函数，组函数不同于前面介绍和使用的函数(单行函数)。组函数可以对分组的数据进行求和、求平均值等运算。组函数只能应用于SELECT子句、HAVING子句或ORDER BY子句中。组函数也可以称为统计函数。

组函数:
AVG:求平均值
COUNT:求计数值，返回非空行数，*表示返回所有行
MAX:求最大值
MIN:求最小值
SUM:求和
STDDEV:求标准偏差，是根据差的平方根得到的
VARIANCE:求统计方差

分组函数中SUM和AVG只应用于数值型的列，MAX、MIN和COUNT可以应用于字符、数值和日期类型的列。组函数忽略列的空值。
使用GROUP BY 从句可以对数据进行分组。所谓分组，就是按照列的相同内容，将记录划分成组，对组可以应用组函数。
如果不使用分组，将对整个表或满足条件的记录应用组函数。
在组函数中可使用DISTINCT或ALL关键字。ALL表示对所有非NULL值(可重复)进行运算(COUNT除外)。DISTINCT 表示对每一个非NULL值，如果存在重复值，则组函数只运算一次。如果不指明上述关键字，默认为ALL。

求有佣金的雇员人数。
执行以下查询：

Sql代码

1. SELECT COUNT(comm) FROM emp;

返回结果为：

Sql代码

1. COUNT(COMM)

---------------------
4

说明：在本例中，没有返回全部雇员，只返回佣金非空的雇员，只有4个人。

求雇员表中不同职务的个数。
执行以下查询：

Sql代码

1. SELECT COUNT( DISTINCT job) FROM emp;

返回结果为：

Sql代码

1. COUNT(DISTINCT JOB)

-------------------------------
5

说明：该查询返回雇员表中不同职务的个数。如果不加DISTINCT，则返回的是职务非空的雇员个数。

分组统计
通过下面的训练，我们来了解分组的用法。
按职务统计工资总和。
步骤1：执行以下查询：

Sql代码

1. SELECT job,SUM(sal) FROM emp GROUP BY job;

执行结果为：

Sql代码

JOB SUM(SAL)

2. ----------------- -------------------

3. ANALYST 6000

4. CLERK 4150

5. MANAGER 8275

6. PRESIDENT 5000

7. SALESMAN 5600

说明：分组查询允许在查询列表中包含分组列，对以上实例，因为是按职务job分组的，所以在查询列中可以包含job字段，使统计结果很清楚
职务为ANALYST的雇员的总工资为6000，职务为CLERK的雇员的总工资为4150，依此类推。
注意：在查询列中，不能使用分组列以外的其他列，否则会产生错误信息。

错误写法:SELECT ename,job,SUM(sal) FROM emp GROUP BY job;

多列分组统计
可以按多列进行分组，以下是按两列进行分组的例子。
按部门和职务分组统计工资总和。
执行以下查询：

Sql代码

1. SELECT deptno, job, sum(sal) FROM emp

2. GROUP BY deptno, job;

执行结果为：
    DEPTNO JOB        SUM(SAL)
------------------ --------- -----------------------
10 CLERK         1300
        10MANAGER      2450
        10 PRESIDENT    5000
        20ANALYST      6000
        20CLERK         1900
        20MANAGER       2975
        30CLERK          950
        30MANAGER       2850
        30SALESMAN      5600
说明：该查询统计每个部门中每种职务的总工资。
分组统计结果限定
对分组查询的结果进行过滤，要使用HAVING从句。HAVING从句过滤分组后的结果，它只能出现在GROUP BY从句之后，而WHERE从句要出现在GROUP BY从句之前。
统计各部门的最高工资，排除最高工资小于3000的部门。
执行以下查询：

Sql代码

1. SELECT deptno, max(sal) FROM emp

GROUP BY deptno
HAVING max(sal)>=3000;

执行结果为：

Sql代码

1. DEPTNO MAX(SAL)

------------------ ------------------
10 5000
20 3000

说明：结果中排除了部门30，因部门30的总工资小于3000。
注意：HAVING从句的限定条件中要出现组函数。如果同时使用WHERE条件，则WHERE条件在分组之前执行，HAVING条件在分组后执行。

分组统计结果排序
可以使用ORDER BY从句对统计的结果进行排序，ORDER BY从句要出现在语句的最后。
按职务统计工资总和并排序。
执行以下查询：

Sql代码

1. SELECT job 职务, SUM(sal) 工资总和 FROM emp

2. GROUP BY job

3. ORDER BY SUM(sal);

执行结果为：

Sql代码

1. 职务工资总和

2. ---------------- ------------------

3. CLERK 4150

4. PRESIDENT 5000

5. SALESMAN 5600

6. ANALYST 6000

7. MANAGER 8275

组函数的嵌套使用
在如下训练中，使用了组函数的嵌套。
求各部门平均工资的最高值。
执行以下查询：

Sql代码

1. SELECT max(avg(sal)) FROM emp GROUP BY deptno;

执行结果为：

Sql代码

1. MAX(AVG(SAL))

-----------------------
2916.66667

说明：该查询先统计各部门的平均工资，然后求得其中的最大值。
注意：虽然在查询中有分组列，但在查询字段中不能出现分组列。