Oracle查询入门（四）分组、行转列

一、分组

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

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

1.1结构

现在的结构如下
select distinct *| 字段 | 表达式 | 函数| as 别名
from 数据源 别名
where 行过滤条件
group by 分组
having 组过滤信息
order by 排序字段 asc | desc ;

1.2执行流程

执行流程 : from -> where -> group by -> having -> select -> order by

1.3注意事项

group by : 分组
1)、select出现分组函数,就不能使用 非分组信息，可以使用group by 字段
2)、group by字段 可以不出现 select 中 ，反之select 除组函数外的，其他字段必须出现在group by中

having : 过滤组
1)、where : 过滤行记录，不能使用组函数
2)、having : 过滤组 可以使用组函数

where中不能使用字段别名，不能使用组函数，因为执行流程问题

1.4例子

-- 求出每个部门的平均工资
select deptno,avg(sal) from emp group by deptno;

-- 找出20部门和30部门的最高工资 
select deptno,max(sal) from emp group by deptno;
--先过滤后分组
select  deptno,max(sal) from emp where deptno in(20,30) group by deptno;
--先分组后过滤
select  deptno,max(sal) from emp group by deptno having deptno in(20,30);

-- 求出10和20部门部门的哪些工资高于1000的员工的平均工资
select avg(sal) from emp where sal>1000 and deptno in(10,20) group by deptno;
select avg(sal) from emp where sal>1000 group by deptno having deptno in(10,20);

-- 找出每个部门的最高工资
select deptno,max(sal) from emp group by deptno;

-- 求出平均工资高于2000的部门编号和平均工资
select deptno,avg(sal) from emp group by deptno having  avg(sal)>2000;

-- 查询最低平均工资的部门编号
--1)找到每个部门的部门编号与平均工资
select deptno,avg(sal) from emp group by deptno;
--2)最低平均工资
select min(avg(sal)) from emp group by deptno;
--3)工资与最低平均工资值相等的部门的部门编号
select deptno,avg(sal) from emp group by deptno having avg(sal)=(最低平均工资);
select deptno
  from emp
 group by deptno
having avg(sal) = (select min(avg(sal)) from emp group by deptno);

-- 统计每个部门的员工数，和部门编号,按照员工个数升序排序
select deptno,count(1) n from emp  group by deptno order by n ;

-- 查询每个工种的最高工资以及工种
select job,max(sal) from emp group by job;

-- 查询平均工资在1500到2000之间的部门平均工资和部门编号
select deptno,avg(sal) from emp group by deptno having avg(sal) between 1500 and 2000;

1.5练习

创建以下表：

--创建表结构
 create table tb_student(
 id number(4) ,
 name varchar2(20),
 course varchar2(20),
 score number(5,2)
 );
 --输入表数据
 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);
 --提交表，提交后不可回滚
 commit;
 --删除整个表
 drop table tb_student cascade constraints;

--使用一条sql语句，查询每门课都大于80分的学生姓名
--数据 : name
--来源 : tb_student
--条件 : 所考课程数=课程总数  and  所有成绩最小分数>80

--一共多少门课程 
select count(distinct course) from tb_student;

--查询
select name
  from tb_student
 group by name
having count(1) = (select count(distinct course) from tb_student) and min(score) > 80;

二、行转列

对上一个例子进行行转列操作

--找出课程名(表头)
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;

输出结果：