Oracle-7 分组函数与子查询_oracle 求和 子查询和分组哪个效率高

---

分组函数

min() 最小值 max()最大值：可以作用于任何数据类型
查询最早 与 最晚的入职日期
select min(hiredate),max(hiredate) from emp;–日期类型
select min(sal),max(sal) from emp; –数值类型
select min(ename),max(ename) from emp; –字符类型

sum()求和 avg()求平均值 ： 仅对数值类型有效
查询所有员工的总工资、平均工资
select sum(sal),avg(sal) from emp;

count() 统计符合条件的行数（记录）
查询部门30有多少个员工
select count(*) from emp where deptno=30; – 6
select count(1) from emp where deptno=30; – 6 效率高
select count(ename) from emp where deptno=30; –6

返回满足条件的非空(NULL)行的数量
查询部门30有多少个员工领取奖金。
select count(comm) from emp where deptno=30; –4

除了COUNT（*）之外，其它所有分组函数都会忽略列中的空值，然后再进行计算
查询所有员工的平均奖金
select sum(comm),avg(comm) from emp; –2200/4 = 550 null被排除
select sum(comm),avg(nvl(comm,0)) from emp;–2200/24=91.6666666666667

---

使用group by子句

根据单列分组
查询每个部门的编号，平均工资
select deptno,avg(sal) from emp group by deptno;

根据多列分组
查询每个部门每个岗位的工资总和。
select deptno,job,sum(sal) from emp group by deptno,job;

查询每个部门的人数
select deptno,count(ename) from emp group by deptno;

---

having子句：结合分组函数或group by子句使用，分组函数可以出现在select、having、order by子句

使用限制条件，排除组数据
查询每个部门最高薪资大于2900的信息
select deptno,max(sal) from emp group by deptno having max(sal) > 2900;

select语句的执行流程
select deptno, job, avg(sal) –5
from emp –1
where job in (‘SALESMAN’, ‘MANAGER’, ‘CLERK’) –2
group by deptno, job –3
having avg(sal) > 1000 –4
order by 3 desc; –6

分组函数嵌套：最多只允许嵌套两层
显示每个部门中平均薪水的最大值
select max(avg(sal)) from emp group by deptno;
select round(max(avg(sal))) from emp group by deptno;

使用子查询
–子查询（内部查询）优先于主查询（外部查询）执行
–子查询的结果被主查询使用
查询出比JONES工资高的其他雇员
select * from emp where sal > (select sal from emp where ename =’JONES’);

---

单行子查询：返回一行一列的值,使用单行运算符： < <= > >= <> =

显示和雇员7369从事相同工作并且工资大于雇员7876的雇员的姓名和工作。
–雇员7369从事的工作
select job from emp where empno=7369;–CLERK
–雇员7876的工资
select sal from emp where empno=7876;–1100.00
–显示工作为’CLERK’并且工资大于1100.00的雇员的姓名和工作
select * from emp
where job = (select job from emp where empno=7369)
and sal > (select sal from emp where empno=7876);

子查询中使用组函数
查询工资最低的员工姓名，岗位及工资
select min(sal) from emp;–800
select ename,job,sal from emp where sal = (select min(sal) from emp);

HAVING子句中使用子查询
查询部门最低工资比20部门最低工资高的部门编号及最低工资
select min(sal) from emp where deptno = 20; –800
select deptno,min(sal) from emp group by deptno having min(sal) > (select min(sal) from emp where deptno = 20);

查询哪个部门的员工人数高于各部门平均人数
select avg(count(1)) from emp group by deptno;–各部门平均人数
select deptno,count(1) from emp group by deptno having count(1) > (select avg(count(1)) from emp group by deptno);

---

多行子查询：返回多行一列的值，使用多行运算符：in any all

in 使用
查询是经理的员工姓名，工资
select distinct mgr from emp where mgr is not null;
–7839,7782,7698,7902,7566,7788
select ename,job from emp where empno in(select distinct mgr from emp where mgr is not null);

any 使用 any =any(与in功能相同)
查询是经理的员工姓名，工资
select distinct mgr from emp where mgr is not null;
–7839,7782,7698,7902,7566,7788
select ename,job from emp where empno =any (7839,7782,7698,7902,7566,7788);

查询部门编号不为10，且工资比10部门【任意】一名员工工资【高】的员工编号，姓名，职位，工资。
select sal from emp where deptno=10;–2450.00,5000.00,1300.00
select empno,ename,job,sal from emp where sal >any(select sal from emp where deptno=10) and deptno <> 10;

查询部门编号不为10，且工资比10部门【任意】一名工资【低】的员工编号，姓名，职位，工资。
select sal from emp where deptno=10;–2450.00,5000.00,1300.00
select empno,ename,job,sal from emp where sal

多列子查询：返回多行多列的值，使用多行运算符：in

成对比较
查询出和1981年入职的任意一个员工的部门和职位【完全相同】员工姓名、部门、职位、入职日期,不包括1981年入职员工
select deptno,job from emp where hiredate between to_date(‘1981-01-01’,’YYYY-MM-DD’) and to_date(‘1981-12-31’,’YYYY-MM-DD’);
select deptno,job from emp where substr(hiredate,-2,2) = ‘81’;–31-12月-81
select deptno,job from emp where extract(year from hiredate) = ‘1981’;
select deptno,job from emp where to_char(hiredate,’YYYY’) = ‘1981’;

select ename,deptno,job,hiredate
from emp
where (deptno, job) in –列名之间逗号’,’隔开，所有列名用（）扩号修饰
(select deptno, job from emp where to_char(hiredate, ‘YYYY’) = ‘1981’)
and to_char(hiredate,’YYYY’) <> ‘1981’

不成对比较
查询出和1981年入职的任意一个员工的部门【或】职位【相同】员工姓名、部门、职位、入职日期,不包括1981年入职员工。
select ename, deptno, job, hiredate
from emp
where (deptno in (select deptno from emp where to_char(hiredate, ‘YYYY’) = ‘1981’)
or job in (select job from emp where to_char(hiredate, ‘YYYY’) = ‘1981’))
and to_char(hiredate,’YYYY’) <> ‘1981’

子查询中的null空值
查询不是经理的员工姓名
select distinct mgr from emp;–所有的经理编号,其中包括一行null值
select * from emp where empno not in(select distinct mgr from emp where mgr is not null);–正确，子查询排除null值

在 FROM 子句中使用子查询
查询比自己部门平均工资高的员工姓名，工资，部门编号，部门平均工资
select deptno,avg(sal) from emp group by deptno;–各部门平均工资
select e.ename, e.sal, e.deptno, a.avgsal
from emp e, (select deptno, avg(sal) avgsal from emp group by deptno) a
where e.deptno = a.deptno
and e.sal > a.avgsal;

rownum 伪列:永远从1开始,不能使用>,=,>=,between..and区间运算做比较运算，只能使用<或者<=
select rownum,e.* from emp e where rownum<=3;
select rownum,e.* from emp e order by rownum desc;–rownum可以与order by结合使用

rowid 伪列，物理存在，唯一标识每一行数据，可以通过rowid修改数据或删除数据
select rowid,e.* from emp e where rowid=’AAAR3sAAEAAAACXAAH’; –AAAR3sAAEAAAACXAAB

---

TOP-N查询:查询指定某列前N条数据

查询员工表中前五名薪资最【高】的员工信息
select rownum,e.* from (select * from emp order by sal desc) e where rownum <= 5;

查询员工表中前五名薪资最【低】的员工信息
select rownum,e.* from (select * from emp order by sal asc) e where rownum <= 5

---

数据库分页查询

已知：每页显示5条，当前页数1
1 1-5
2 6-10
3 11-15
4 16-20
开始索引：（当前页码每页显示条数）-每页显示条数 + 1 = （当前页码-1）每页显示条数+1
结束索引：当前页码*每页显示条数

select rownum,e.* from emp e where rownum <= 5; –第一页
select * from (select rownum rn,e.* from emp e where rownum <=10) where rn>=6; –第二页 效率高
select * from (select rownum rn,e.* from emp e) where rn<=10 and rn>=6;
–第二页 效率低
select * from (select rownum rn,e.* from emp e where rownum <=15) where rn>=11; –第三页
select * from (select rownum rn,e.* from emp e) where rn<=15 and rn>=11; –第三页

&表示一个变量，beginIndex、endIndex分别为变量名称，可自定义
select * from (select rownum rn,e.* from emp e where rownum <=&endIndex) where rn>=&beginIndex;

分页公用SQL语句
select * from (select rownum rn,e.* from emp e) where rn<=(当前页码每页显示条数) and rn>=(（当前页码-1）每页显示条数+1);