group by分组 行转列 表连接

1.分组

group by 分组字段 having 过滤字段

​ 分组函数可以过滤组信息，having后面可以使用组函数。在使用group by时，select后面可以使用多行函数也可以使用字段，不过字段必须与分组字段相同，且having后面的字段也如此。在任何时候where后面都不可以使用分组函数。

--分组函数，先分组后过滤，select ,having后的字段只能是group by后已有的
select avg(sal) avg_sal,job,deptno from emp group by deptno,job having deptno=10;
--查询 最低平均工资的部门编号
select min(avg_sal) from (select avg(sal) avg_sal from emp group by deptno);
--查询10,30部门的平均工资与薪资总和
select avg(sal),sum(sal) from emp group by deptno having deptno in(10,30);
--查询10,20部门中最高平均工资的部门名称
select max(avg(sal)) from emp group  by deptno and deptno;
select deptno from emp group by deptno having avg(sal) in(select max(avg(sal)) from emp group  by deptno) and deptno in(10,20);

2.行转列

​ 当我们将一个表的行转列时，需要用到decode函数；

--创建表
create table tb_student(
id number(4) ,
name varchar2(20),
course varchar2(20),
score number(5,2)
);
--============
select * from tb_student;
--===================
--插入字段内容
insert into tb_student values(1,'张三','语文',81);
insert into tb_student values(2,'张三','数学',75);
insert into tb_student values(3,'李四','语文',81);
insert into tb_student values(4,'李四','数学',90);
insert into tb_student values(5,'王五','语文',81);
insert into tb_student values(6,'王五','数学',100);
insert into tb_student values(7,'王五','英语',90);
--====================
--查询所有课程成绩都大于80学成的姓名
--查询一共有几门课程
select count(distinct course) from tb_student;
--查询每门课都大于80学生
select name
  from tb_student
 group by name
having count(course) = (select count(distinct course) from tb_student) and min(score) > 80;
--====================
--行转列
select * from tb_student;
--查询每个学生的每门课成绩
select name,
       decode(course, '语文', score) 语文,
       decode(course, '数学', score) 数学,
       decode(course, '英语', score) 英语
  from tb_student;
--按照姓名进行分组==使用了group by，但是decode是单行函数，所以要将单行函数转为组函数
select name,
       max(decode(course, '语文', score)) 语文,
       max(decode(course, '数学', score)) 数学,
       max(decode(course, '英语', score)) 英语
  from tb_student
 group by name;

3.表连接

​ 当需要查询的数据来自于多张表中时，需要使用表连接。

• 92语法 没有连接条件|行过滤条件

92语法中，多表连接时每个表之间用，号连接，其格式为：

select 数据 from 数据源1，数据源2，…；

​ 在没有添加任何连接条件或行过滤条件时，数据源中的数据分别连接为对乘的效果，即笛卡尔积。

--等值连接  笛卡尔积
select * from emp e,dept d;

• 有连接条件|行过滤条件

select 数据 from 数据源1，数据源2，…where 连接条件|行过滤条件；

​ 注：一条sql中出现多个数据源，建议提供表的别名，且指定查询结果集中的字段名，如果出现同名需指明出处。

--笛卡尔积不满足条件，需要过滤  等值连接
select * from emp e,dept d where e.deptno=d.deptno;
--非等值连接
select t.ename from ( select * from emp e,dept d where e.deptno=d.deptno) t,emp e where  e.MGR=t.empno;

​ 当有些数据不满足连接条件 ，但是还是想显示，可以选择使用外连接。在连接条件中主表的对面（+），主表在左为左连接，主表在右表示右连接。

--当有些数据不满足连接条件但是仍然需要显示，需要使用外连接（左连接|由连接）==连接条件中主表的对面增加(+)
select * from emp e1,emp e2 where e1.mgr=e2.empno(+);