SQL 基础之组函数（九）

组函数:

– 类型和语法

– 使用 AVG, SUM, MIN, MAX, COUNT

– 组函数使用 DISTINCT 关键字

– 组函数中NULL 值

分组函数:作用于一组数据，并对一组数据返回一个值

组函数类型

• AVG 平均值

• COUNT 统计值

• MAX 最大值

• MIN 最小值

• SUM 合计

• STDDEV 标准差

• VARIANCE 方差

组函数语法：

select group_function(column), ... from table [where condition] [order by column];

使用 AVG 和 和 SUM 函数

可以对数值型数据使用 AVG 和 SUM 函数

1、查询job_id为REP的 平均工资，最高工资，工资总和

select avg(salary),max(salary),min(salary),sum(salary) from employees where job_id like '%REP%';

使用 MIN 和 和 MAX 

可以对数值型、字符型和日期型使用 MIN 和 MAX 函数

2、查询入职时最短和最长时间

select min(hire_date),max(hire_date) from employees;

使用 COUNT 

1、统计一下department_id 为50的部门有多少人

select count(*) from employees where department_id =50;

2、如果有空值不会被计算进去

select count(commission_pct)  from employees where department_id=80;

3、显示 EMPLOYEES 表中不同的部门数

select count(distinct department_id) from employees;

组函数忽略空值

1、统计一下提成

select avg(commission_pct) from employees;

2、将所有的人都统计进来

select avg(nvl(commission_pct,0)) from employees;

分组数据：GROUP BY

• 可以使用GROUP BY 子句将表中的数据分成若干组.

• group by 后面不能使用列别名，select 后面有限制.

1、求出EMPLOYEES中各个部门的平均工资

select department_id,avg(salary) from employees group by department_id order by department_id;

2、包含在 GROUP BY 子句中的列不必包含在SELECT 列表中。

select sum(salary) from employees group by job_id;

3、进行多组分列，按照部门和工作进行分组得到分组后工资的和

select department_id,job_id,sum(salary) from employees group by department_id,job_id order by department_id;

非法使用组函数

SELECT 列表中的列或表达式，未包含在组函数中的列，都必须包含于GROUP BY 子句中

错误：

select department_id, count(last_name) from employees;

或

select department_id, job_id, count(last_name) from employees group by department_id;

也就是说必须把department_id 和job_id 加入到group by 中

正确：

select department_id, count(last_name) from employees group by department_id;

或

select department_id, job_id, count(last_name) from employees group by department_id，job_id;

• 不能使用 WHERE 子句来过滤组

• 可以使用 HAVING 子句来过滤组

  
  

错误：

select department_id, avg(salary) from employees where avg(salary) > 8000 group by department_id;

过滤分组：HAVING  子句

使用 HAVING 子句过滤分组条件：

• 行已经被分组。

• 使用了组函数。

• 满足HAVING 子句中条件的分组将被显示

语法：

select column, group_function from table [where condition]

[group by group_by_expression]

[having group_condition]

[order by column];

1、每个部门的最高薪水大于$10,000

select department_id,max(salary) from employees group by department_id having max(salary)>10000;

2.查找不是REP工作的工资总和大于13000的，并按照sum salary排序。

select job_id ,sum(salary) from employees where job_id not like '%REP%' group by job_id having sum(salary) > 13000

order by sum(salary);

嵌套组函数

按照部门分类显示平均工资的最大值：

select  max(avg(salary)) from employees group by department_id;

但是嵌套组函数好像不能在添加新的列了

练习题：

1、找出所有员工工资的最大值，最小值和以及平均值。并以此将各列的别名修改

为”Maximum”,”Minimum”,”Sum”,”Average”。并且要求将结果进行四舍五入。

select round（max(salary) ，0） "Maxinmum", round(min(salary),0) Minimum, round(sum(salary),0) Sum ,round(avg(salary),0) Average from employees;

2、以 job_id 进行分组，查看每个工种的工资的最大值，最小值，和，以及平均值

select  job_id,max(salary),min(salary),sum(salary),avg(salary) from employees group by job_id;

3、写一个查询语句，统计每一个工种的员工数

select job_id,count(employee_id）from employees group by job_id;

4、让HR部门的同事可以输入一个工种，然后 sql 返回该工种的员工数量。

select job_id,count(*) from employees where job_id like '&job_title' group by job_id;

5、直接显示出所有经理的总人数。并将该列标记为"Number of Managers".提示：使用 MANAGER_ID 这一列来确定经理的数量

select count(distinct manager_id) "Number of Managers"  from employees;

6、查出薪水最高的和薪水最低的差值，并将该列标记为 DIFFERENCE

select max(salary) - min(salary) "DIFFERENCE"  from employees;

7、请查询出每个经理手下工资最低的员工，那些没有经理的员工需要排除，并且需要排除那些最低薪水

小于等于6000 组。最后将结构根据薪水以降序排列。

select manager_id,min(salary)

from employees

where manager_id is not null

group by manager_id

having min(salary) > 6000

order by min(salary) desc;

8、请编写一条 SQL 语句，查看员工的总数，以及在 1996,1997,1998,1999 这几年被雇佣的员工数量，并为各列取合适的别名。

select count(*) total,

sum(decode(to_char(hire_date, 'fm YYYY'),1999,1,0)) "1999",

sum(decode(to_char(hire_date, 'fm YYYY'),1998,1,0)) "1998",

sum(decode(to_char(hire_date, 'fm YYYY'),1997,1,0)) "1997",

sum(decode(to_char(hire_date, 'fm YYYY'),1996,1,0)) "1996"

from employees;

9、请通过一个矩阵显示出所需要的结果，要求是根据部门编号（20,50,80,90）算出对应的工种的工

资，以及该工种的工资总和，对于部门号是 20,50,80,90 这几列来说，请给出一个合适的别名。

select job_id "job",

sum(decode(department_id,20,salary)) "dept 20",

sum(decode(department_id,50,salary)) "dept 50",

sum(decode(department_id,80,salary)) "dept 80",

sum(decode(department_id,90,salary)) "dept 90",

sum(salary) "total"

from employees

group by job_id;