ORACLE组

一、AVG、SUM、MIN、MAX、COUNT
AVG

SUM

MIN

MAX

COUNT

统计筛选行数

特殊的使用

二、GROUP BY、HAVING
GROUP BY
SELECT department_id, avg(salary) FROM employees
GROUP BY department_id;

对于指定列进行筛选分组

SELECT avg(salary) FROM employees
GROUP BY department_id;


如果返回值为组合函数，则不需要筛选列出现在GROUP BY 后
如果返回值为中不是组合函数，又不在GROUP BU后则会报错

SELECT department_id, job_id,trunc(avg(salary)) FROM employees
WHERE department_id>50
GROUP BY department_id,job_id
ORDER BY department_id,job_id

SELECT department_id, job_id,trunc(avg(salary)) FROM employees
WHERE department_id>50 AND trunc(avg(salary))>8000
GROUP BY department_id,job_id
ORDER BY department_id,job_id;

组合函数不能进行WHERE运算筛选

HAVING
解决WHERE不能筛选运算问题
SELECT department_id, job_id,trunc(avg(salary)) FROM employees
WHERE department_id>50
GROUP BY department_id,job_id
HAVING trunc(avg(salary))>8000
ORDER BY department_id,job_id;

SELECT department_id, job_id,trunc(avg(salary)) AVGSAL FROM employees
WHERE department_id>50
GROUP BY department_id,job_id
HAVING AVGSAL>10000
ORDER BY department_id,job_id;  --ERR

不能用别名筛选

SELECT max(avg(salary)) FROM employees
GROUP BY department_id;

支持嵌套特性