[玩转SQL]:04 组函数

组函数

特点是输入多行数据,输出一行数据.

常见组函数

AVG	取平均值
COUNT	统计行
MAX	取最大值
MIN	取最小值
SUM	求和

语法:

SELECT  group_function(column), ...
FROM    table
[WHERE  condition]
[ORDER  BY column];

--查看薪水平均值、最大值、最小值、总和
SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary)
FROM   employees
WHERE  job_id LIKE '%REP%';

字符串以及日期可以使用MAX、MIN函数.

SELECT COUNT(*)
FROM   employees;

等同于

SELECT COUNT(1)
FROM   employees;

COUNT(1)的执行速度稍快一些.

SELECT COUNT(commission_pct)
FROM   employees;

在满足COUNT内部表达式的情况下非NULL的行进行统计.

 

组函数会自动抛弃NULL值.

可以使用NVL来避免NULL值.

SELECT AVG(commission_pct)
FROM   employees;

SELECT AVG(NVL(commission_pct,0))
FROM   employees;

可见加不加NVL结果完全不同.

GROUP 子句

语法:

SELECT    column, group_function(column)
FROM      table
[WHERE    condition]
[GROUP BY group_by_expression]
[ORDER BY column];

column必须包含在 group_by_expression中.

若WHERE与GROUP BY同时存在首先运行WHERE.

GROUPBY于WHERE相同不能使用列别名.

--统计各部门的平均工资
SELECT   department_id, AVG(salary)
FROM     employees
GROUP BY department_id;

--统计各部门、各职位的工资总和
SELECT   department_id, job_id, SUM(salary)
FROM     employees
GROUP BY department_id, job_id
ORDER BY department_id;

不可以在WHERE中使用组函数.

因为SQL的执行顺序为WHERE -> GROUP BY  -> 组函数

为了避免这种情况,可以使用HAVING.

加入HAVING的SQL执行顺序为WHERE->GROUP BY -> 组函数->HAVING

HAVING 子句

语法:

SELECT    column, group_function(column) 
FROM      table 
[WHERE    condition] 
[GROUP BY group_by_expression] 
[HAVING   group_condition]
[ORDER BY column];

一般来说HAVING放在GROUP BY之后.

--查询各部门工资最高的
SELECT   department_id, MAX(salary)
FROM     employees
GROUP BY department_id
HAVING   MAX(salary) > 1000;

--------------------------------------------------------------------------------------
版权所有,转载时必须以链接方式注明源地址,否则追究法律责任!

QQ    :   413844436
Email  :   softomg@163.com
Blog   :   http://blog.youkuaiyun.com/softomg