SQL 基础之组函数（九）

menny

　　组函数:
　　– 类型和语法
　　– 使用 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;