Oracle数据库 —— SQL之select语句(5)

前端之家收集整理的这篇文章主要介绍了Oracle数据库 —— SQL之select语句(5)前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

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;


@H_403_24@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;

猜你在找的Oracle相关文章