SQL 基础之组函数（九）
组函数:– 类型和语法
– 使用 AVG, SUM, MIN, MAX, COUNT
– 组函数使用 DISTINCT 关键字
– 组函数中NULL 值
分组函数:作用于一组数据，并对一组数据返回一个值
组函数类型
[*] AVG 平均值
[*] COUNT 统计值
[*] MAX 最大值
[*] MIN 最小值
[*] SUM 合计
[*] STDDEV 标准差
[*] VARIANCE 方差
组函数语法：
select group_function(column), ... from table ;
使用 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
;
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);
嵌套组函数
按照部门分类显示平均工资的最大值：
selectmax(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 进行分组，查看每个工种的工资的最大值，最小值，和，以及平均值
selectjob_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;
页:
[1]