示例详解Oracle 分组函数用法

核心提示：order by 中可以使用别名，where 和 group by 中不允许使用别名在 SELECT 列表中出现的所有列，示例详解Oracle 分组函数用法(2)，只要不在 group 函数中，都必须包含在group by 子句中，示例：按照部门分组，把每个部门的平均工资统计出来SELECT DEPARTMENT_ID

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