Chapter5使用组函数.doc

OCP-007-Chapter5 使用组函数 目的学完本课后，可以完成下列事情：了解可用的组函数描述组函数的使用使用Group By子句对行进行分组使用Having子句对分组后的行进行排除或选择组函数在一组行的集合上面操作，每组返回一个值组函数的类型：（每个组函数接受一个参数，忽略null值）AVG(DISTINCT|ALLn) -缺省为ALLCOUNT(*|DISTINCT|ALLexpr) -缺省为ALL，使用*会选择重复行和null值MAX(DISTINCT|ALLexpr)，MIN(DISTINCT|ALLexpr)，STDDEV(DISTINCT|ALLx)， SUM(DISTINCT|ALLn)，VARIANCE(DISTINCT|ALLx) -缺省都为ALL，忽略null值SELECT column, group_function(column), .FROM tableWHERE conditionGROUP BY group_by_expressionHAVING group_conditionORDER BY column;所有的组函数几乎都忽略null值，要使用nvl,nvl2等函数替换null值MIN和MAX可以用于任何数据类型，AVG、SUM、VARIANCE和STDDEV只能用于数字数据类型SELECT AVG(NVL(commission_pct, 0) -先把commission_pct中的null转换为0FROM employees;select列表里的列名，要么出现在组函数里面，要么出现在Group By子句里面，否则会出现错误where子句用在分组之前把不需要的行排除掉在Group By子句中不能使用列别名Group By子句所依据的列，不一定非要在select子句中出现在order by子句中可以使用组函数使用多列分组，此时会在第一次产生的分组结果之中进行第二次分组，而组函数是对最后的结果进行处理。不能使用where子句限制组（包括组函数），要使用having子句，而且在where子句中不能使用组函数having子句用于行已经分组，组函数已经应用，此时只有满足条件的行才会显示出来组函数可以嵌套，最多两层练习（答案在卷2的296页）1.T 2.F 3.T 4. select max(salary) Maximum, min(salary) Minimum, sum(salary) Sum, round(avg(salary) Average from employees;5. select job_id, max(salary) Maximum, min(salary) Minimum, sum(salary) Sum, round(avg(salary) Average from employeesgroup by job_id;6. select job_id, count(*) from employees group by job_id;7. select count(distinct manager_id) Number of Managers from employees;8. select max(salary)-min(salary) DIFFERENCE from employees;9. select manager_id, min(salary)from employeeswhere manager_id is not nullgroup by manager_idhaving min(salary) 6000order by min(salary) desc10. select d.department_name Name, d.location_id Location, count(*) Number of People, round(avg(e.salary),2) Salaryfrom departments dnatural join employees ewhere e.last_name is not nullgroup by department_name,location_id11. select count(*) total ,sum(nvl(nullif(0,to_char(hire_date,YYYY)-1995),1) 1995, sum(nvl(nullif(0,to_char(hire_date,YYYY)-1996),1) 1996, sum(nvl(nullif(0,to_char(hire_date,YYYY)-1997),1) 1997, sum(nvl(nullif(0,to_char(hire_date,YYYY)-1998),1) 1998 from employees ;注意别名里面的数字要使用双引号包围起来答案：SELECT COUNT(*) total,SUM(DECODE(TO_CHAR(hire_date, YYYY),1995,1,0) 1995,SUM(DECODE(TO_CHAR(hire_date, YYYY),1996,1,0) 1996,SUM(DECODE(TO_CHAR(hire_date, YYYY),1997,1,0) 1997,SUM(DECODE(TO_CHAR(hire_date, YYYY),1998,1,0) 1998FROM employees;可以看到使用decode要简单清晰很多12. select job_id Job, sum(case department_id when 20 then salary end) Depart 20, sum(case department_id when 50 then salary end) Depart 50, sum(case department_id when 80 then salary end) Depart 80, sum(case department_id when 90 then salary end) Depart 90, sum(salary) Totalfrom employeesgroup by job_id答案：SELECT job_id Job,SUM(DECODE(department_id , 20, salary) Dept 20,SUM(DECODE(department_id , 50, salary) Dept 50,SUM(