6 select语句

6.6 组函数与分组

6.6.1 组函数

对一组数据处理之后返回一个结果。

1、常见的组函数：count、max、min、sum、avg
　　求s_emp表中的员工个数 工资的最大值 工资的最小值
　　　　select count(id),max(salary),min(salary) from s_emp;
　　求员工工资的和 和 员工工资的平均值
　　　　select sum(salary),avg(salary) from s_emp;
　　　　
2、组函数中可以使用distinct关键字
　　select sum(distinct salary),avg(distinct salary) from s_emp;

3、组函数对NULL值的处理特点
　　求提成的和和提成的平均值
　　　　select sum(commission_pct),avg(commission_pct) from s_emp;

6.6.2 分组

1、group by 分组标准；
　　按照部门编号把员工数据分组,并统计部门人数
　　　　select dept_id,count(id) from s_emp group by dept_id;
　　　　select count(id) from s_emp where dept_id is null;
　　按照部门号分组统计每个组的平均工资
　　　　select dept_id,avg(salary) from s_emp group by dept_id;

2、对组数据进行过滤 — having
　　要求显示平均工资大于2000的
　　　　select dept_id,avg(salary) from s_emp group by dept_id having avg(salary)>2000;

3、sql的执行顺序
　　select dept_id,avg(salary) asal from s_emp group by dept_id having asal>2000;
第 1 行出现错误:
ORA-00904: “ASAL”: 标识符无效
select dept_id,avg(salary) asal from s_emp group by dept_id having avg(salary)>2000 order by asal;

->from->where->group by->having-> select->order by

4、按照部门号分组 统计每个部门的人数 显示人数大于1的部门
列出dept_id 人数 部门名
　　select dept_id,count(id) from s_emp group by dept_id having count(dept_id)>1;
　　select dept_id,count(e.id),name from s_emp e,s_dept d where e.dept_id=d.id group by dept_id,name having count(e.id)>1;

/在分组语句中select后的字段要么是分组标准，要么是经过合适的组函数处理过的，没有第三种可能/
　　select dept_id,max(name) from s_emp e,s_dept d where e.dept_id=d.id group by dept_id having count(e.id)>1;
因为有可能部门ID不一样，但部门名一样
DEPT_ID COUNT(E.ID) MAX(NAME)
　　42 　　　3 　　　　Operations
　　34 　　　2 　　　　Sales
　　43 　　　2　　　　 Operations
　　44 　　　2 　　　　Operations
　　31 　　　2 　　　　Sales
　　50 　　　2 　　　　Administration
　　41 　　　4 　　　　Operations

　　select dept_id,s_dept d where e.dept_id=d.id(+) group by dept_id having count(e.id)>1;/显示空部门/

6.7 子查询

把一个查询的结果作为另一个查询的基础。

a、子查询用在where后
　　/找出所有的领导员工编号/
　　select distinct manager_id from s_emp;
　　谁是领导？
　　select id,first_name from s_emp where id in(select distinct manager_id from s_emp);
　　谁是普通员工？not in(注意null，和null比判断不了，返回假)
　　select id,first_name from s_emp where id not in(select distinct manager_id from s_emp /* where manager_id is not null */);
　　select id,first_name from s_emp where id not in(select distinct manager_id from s_emp where manager_id is not null);

b、子查询用在having后
　　按照部门号分组，求每个部门的平均工资
　　要求显示大于50部门的平均工资的部门
　　select dept_id,avg(salary) from s_emp group by dept_id having avg(salary)>(select avg(salary) from s_emp where dept_id=50);

c、子查询用在from后面(分页技术中会用到) 　　任何一条sql都对应内存中一张表 　　select id,first_name name,salary from s_emp; 　　select name,salary from (select id,salary from s_emp) where salary>1000;