3.oracle分组函数

3.oracle

3.1分组

分组: group by , 将符合条件的记录 进一步的分组

过滤组:having, 过滤组信息 ，表达式同where 一致

现在的结构如下

select distinct * | 字段 | 表达式 | 函数 as 别名

from 表 表别名
where 过滤行记录条件
group by 分组字段列表
having 过滤组
order by 字段列表 asc | desc

解析步骤

1)、from 2)、where 3)、group 4)、having 5)、 select 6)、order by

group by : 分组

1)、elect出现分组函数,就不能使用 非分组信息，可以使用group by 字段

**2）、group by字段 可以不出现 **select **中 ，反之select **除

组函数外的，其他字段必须出现在group by中

having : 过滤组

1. where : 过滤行记录，不能使用组函数

2. having : 过滤组 可以使用组函数

   --按 部门 查询 平均工资 
   
   select avg(sal) from emp group by deptno;
   
   --按 部门岗位 查询 平均工资 
   
   select avg(sal) from emp group by deptno,job;
   
   --按 部门 查询 平均工资，且平均工资大于2000的部门编号
   
   --1、先分组 后过滤 (不推荐) 
   
   select *
   
   from (select deptno, avg(sal) avsal from emp 
   
   where 1 = 1 group by deptno) 
   
   where avsal > 2000;
   
   --2、过滤组 ,分组同时 过滤 
   
   select avg(sal), deptno from emp group by 
   
   deptno having avg(sal)>2000;
   

   --查询 最低平均工资的部门编号
   
   --1)、按部门求出平均薪水 
   
   select avg(sal) from emp group by deptno;
   
   --2)、找出最低的平均薪水 
   
   select min(avg(sal)) from emp group by deptno;
   
   --3)、过滤组 
   
   select deptno
   
   from emp 
   
   where 1 = 1 
   
   group by deptno 
   
   having avg(sal) = (select min(avg(sal)) from 
   
   emp where 1 = 1 group by deptno);
   

   --查看 高于本部门平均薪水员工姓名
   
   --1、按部门求出平均薪水
   
   --2、关联子查询 
   
   select *
   
   from emp e 
   
   where exists 
   
   (select deptno
   
   from (select deptno, avg(sal) avgsal
   
   from emp group by deptno) e2 
   
   where e.deptno = e2.deptno 
   
   and e.sal > avgsal);
   
   --另外一种 (推荐) 
   
   select *
   
   from emp e1
   where sal > (select avg(sal) from emp e2 
   sql
   where e2.deptno = e1.deptno);
   

   3.2 行转列

转成如下

方便: 解耦（与记录之间）+扩展(与结构之间)

行转列

--找出课程名(表头) 

select distinct course from tb_student;

--数据(行记录) 分组（学生+行转列 decode） 

select * from tb_student;

--1、行转列 decode
select name, decode(course, '语文', score) 语文, decode(course, '数学', score) 数学, decode(course, '英语', score) 英语
from tb_student;
--2、分组 select name, min(decode(course, '语文', score)) 语文, min(decode(course, '数学', score)) 数学, min(decode(course, '英语', score)) 英语
from tb_student group by name;

3.3表连接

当我们获取的数据不是来自于同一张表而是来自于多张

表时就需要使用到表连接

elect * from emp;

select * from dept;

注意:同名列 非* 必须区分

数据源 、关系列、 过滤条件、字段

3.3.1笛卡尔积

非* 必须区分 使用表名 或别名.区分

select * from emp , dept; 

select ename , dname from emp , dept; 

select ename, dname, e.deptno from emp e, dept 

d;

3.3.2 等值连接(在笛卡尔积基础上取条件列相同的值)

--员工名称及部门名称 

select ename, dname, e.deptno from emp e, dept 

d where e.deptno = d.deptno;

--找出30部门的员工名称及部门名称:先关联后过滤 

select ename, dname, e.deptno

from emp e, dept d 

where e.deptno = d.deptno 

and e.deptno = 30;

--记录很多时 :先过滤后关联

-- 数据来源： emp (select * from emp where 

deptno=30) e , dept(select * from dept where 

deptno=30) d 

select * from emp where deptno = 30; 

select * from dept where deptno = 30;

-- 查询的字段：ename, dname, e.deptno

-- 条件：e.deptno=d.deptno , deptno=30 

select ename, dname, e.deptno

from (select * from emp where deptno = 30) 

e, 

(select * from dept where deptno = 30) 

d

where e.deptno = d.deptno;

3.3.4 非等值连接

--查询员工姓名，工资及等级

--900 属于哪个等级 

select grade

from salgrade
where 900 > losal and 900 < hisal; select grade from salgrade where 900 between losal and hisal;
--查询员工姓名，工资及等级
-- 数据源： emp e, salgrade s
-- 字段： ename, grade, sal
-- sal between losal and hisal select ename, grade, sal
from salgrade s, emp e where sal between losal and hisal;

3.3.5 自连接

--找出 存在上级的员工姓名 及上级名称
-- 数据来源： emp e, emp m
-- 字段： e.ename, m.ename
-- 条件： e.mgr=m.empno select e.ename, m.ename from emp e, emp m where e.mgr = m.empno;

3.3.6 外连接

--找出 所有的员工姓名 及上级名称

--找出 所有部门的员工数 及部门名称 

select dname, nu

from dept d, (select count(1) nu, deptno

from emp group by deptno) e 

where d.deptno(+) = e.deptno; 

看逗号, 主表在,的左边就叫左连接

ame from emp e, emp m where e.mgr = m.empno;

### 3.3.6 外连接

```sql
--找出 所有的员工姓名 及上级名称

--找出 所有部门的员工数 及部门名称 

select dname, nu

from dept d, (select count(1) nu, deptno

from emp group by deptno) e 

where d.deptno(+) = e.deptno; 

看逗号, 主表在,的左边就叫左连接

​ 主表在,的右边叫右连接