Oracle 分组函数用法示例详解

hiaf055u6m

聚合函数、多行函数、分组函数都是一类函数
GROUP BY 和 HAVING


group 函数：AVG\SUM\MIN\MAX\COUNT\STDDEV\VARIANCE
DISTINCT 与 group 函数结合使用
NULL 值在 group函数当中的处理


嵌套 group 函数


group 函数的语法：
SELECT GROUP_FUNCTION(COLUMN),...
FROM TABLE
[WHERE CONDITION]
[ORDER BY COLUMN];


---示例1：AVG\MAX\MIN\SUM针对NUMBER类型数据


SELECT AVG(SALARY), MAX(SALARY), MIN(SALARY), SUM(SALARY)
FROM EMPLOYEES
WHERE JOB_ID LIKE '%REP%';


AVG(SALARY) MAX(SALARY) MIN(SALARY) SUM(SALARY)
----------- ----------- ----------- -----------
8272.72727    11500    6000   273000

---示例2：MIN和MAX可以针对number外还可以针对date类型数据
hr@PROD> SELECT MIN(HIRE_DATE), MAX(HIRE_DATE) FROM EMPLOYEES;


MIN(HIRE_ MAX(HIRE_
--------- ---------
17-JUN-87 21-APR-00


----示例3：COUNT(*) 和 COUNT(1),COUNT(1)的速度比COUNT(*)快
hr@PROD> SELECT COUNT(*) FROM EMPLOYEES;


COUNT(*)
----------
107
-----COUNT(*)返回某个表中的行数
hr@PROD> SELECT COUNT(1) FROM EMPLOYEES;


COUNT(1)
----------
107

---COUNT(EXPR)符合expr 的所有非空值行的行数，请看下例:
hr@PROD> SELECT COUNT(COMMISSION_PCT) FROM EMPLOYEES;


COUNT(COMMISSION_PCT)
---------------------
35




hr@PROD> SELECT COUNT(DEPARTMENT_ID) FROM EMPLOYEES;


COUNT(DEPARTMENT_ID)
--------------------
106


hr@PROD> SELECT COUNT(EMPLOYEE_ID) FROM EMPLOYEES;


COUNT(EMPLOYEE_ID)
------------------
107

------------DISTINCT 和 group 函数的配合使用
示例：
hr@PROD> SELECT COUNT(DISTINCT DEPARTMENT_ID) FROM EMPLOYEES;


COUNT(DISTINCTDEPARTMENT_ID)
----------------------------
11



--------------------------------
-----------group 函数对 Null 值的处理
----group 函数忽略列中的 null 值


hr@PROD> SELECT COUNT(COMMISSION_PCT) FROM EMPLOYEES;


COUNT(COMMISSION_PCT)
---------------------
35




hr@PROD> SELECT COUNT(NVL(COMMISSION_PCT,0)) FROM EMPLOYEES;


COUNT(NVL(COMMISSION_PCT,0))
----------------------------
107

-----35 人参与计算
hr@PROD> SELECT AVG(COMMISSION_PCT) FROM EMPLOYEES;


AVG(COMMISSION_PCT)
-------------------
.222857143


------107 人参与计算
hr@PROD> SELECT AVG(NVL(COMMISSION_PCT,0)) FROM EMPLOYEES;


AVG(NVL(COMMISSION_PCT,0))
--------------------------
.072897196


-------创建分组数据----
GROUP BY 子句


计算每个部门中的平均薪水
SELECT COLUMN ,GROUP_FUNCTION(COLUMN)
FROM TABLE
[WHERE CONDITION]
[GROUP BY GROUP_BY_EXPRESSION]
[ORDER BY COLUMN];


注意：SELECT 子句中的 COLUMN 必须包含在 GROUP BY 子句中
列出的单行必须包含在 group by 子句中
执行顺序，先计算 WHERE,后计算group by，再查询结果，最后执行 order by
order by 中可以使用别名，where 和 group by 中不允许使用别名


在 SELECT 列表中出现的所有列，只要不在 group 函数中，都必须包含在
group by 子句中。


示例：按照部门分组，把每个部门的平均工资统计出来
SELECT DEPARTMENT_ID,AVG(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
ORDER BY DEPARTMENT_ID;


DEPARTMENT_ID AVG(SALARY)
------------- -----------
10    4400
20    9500
30    4150
40    6500
50 3475.55556
60    5760
70    10000
80 8955.88235
90 19333.3333
100    8600
110    10150
7000


12 rows selected.


-------------重点：高级用法
使用 group by 对多列进行分组
SELECT DEPARTMENT_ID DEPT_ID, JOB_ID,SUM(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID, JOB_ID
ORDER BY DEPARTMENT_ID;


DEPT_ID JOB_ID   SUM(SALARY)
---------- ---------- -----------
10 AD_ASST      4400
20 MK_MAN      13000
20 MK_REP      6000
30 PU_CLERK     13900
30 PU_MAN      11000
40 HR_REP      6500
50 SH_CLERK     64300
50 ST_CLERK     55700
50 ST_MAN      36400
60 IT_PROG     28800
70 PR_REP      10000
80 SA_MAN      61000
80 SA_REP     243500
90 AD_PRES     24000
90 AD_VP      34000
100 FI_ACCOUNT    39600
100 FI_MGR      12000
110 AC_ACCOUNT    8300
110 AC_MGR      12000
SA_REP      7000

-------GROUP 函数的非法使用示例：
示例1：
hr@PROD> SELECT DEPARTMENT_ID,COUNT(LAST_NAME)
2 FROM EMPLOYEES;
SELECT DEPARTMENT_ID,COUNT(LAST_NAME)
*
ERROR at line 1:
ORA-00937: not a single-group group function


------必须加一个 group by 子句，包含 DEPARTMENT_ID


示例2：
hr@PROD> SELECT DEPARTMENT_ID, JOB_ID,COUNT(LAST_NAME)
2 FROM EMPLOYEES
3 GROUP BY DEPARTMENT_ID;
SELECT DEPARTMENT_ID, JOB_ID,COUNT(LAST_NAME)
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression


-------必须在 GROUP BY 子句中加上 JOB_ID


-------不得在 WHERE 子句中限制 groups
可以考虑使用 HAVING 子句来限制 groups
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 子句
语法：先 group by，然后 group function，然后 Having
SELECT COLUMN,GROUP_FUNCTION
FROM TABLE
[WHERE CONDITION]
[GROUP BY GROUP_BY_EXPRESSION]
[HAVING GROUP_CONDITION]
[ORDER BY COLUMN];


示例：HAVING 子句
SELECT DEPARTMENT_ID, MAX(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING MAX(SALARY) > 10000;


DEPARTMENT_ID MAX(SALARY)
------------- -----------
100    12000
30    11000
90    24000
20    13000
110    12000
80    14000

-------执行顺序：SELECT\FROM \ WHERE \GROUP BY \HAVING \ORDER BY
SELECT JOB_ID, SUM(SALARY) PAYROLL
FROM EMPLOYEES
WHERE JOB_ID NOT LIKE '%REP%'
GROUP BY JOB_ID
HAVING SUM(SALARY) > 13000
ORDER BY SUM(SALARY);


JOB_ID    PAYROLL
---------- ----------
PU_CLERK    13900
AD_PRES     24000
IT_PROG     28800
AD_VP      34000
ST_MAN     36400
FI_ACCOUNT   39600
ST_CLERK    55700
SA_MAN     61000
SH_CLERK    64300


-------------GROUP 函数的嵌套
最后一个示例：
SELECT MAX(AVG(SALARY))
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;


SELECT MAX(AVG(SALARY))
FROM EMPLOYEES
　　GROUP BY DEPARTMENT_ID;
　　

　　转载请注明作者及原文出处，否则拒绝转载
　　本文来源：http://blog.csdn.net/xiangsir/article/details/8604794