-----------------------------------------------子查询
--1,单行子查询
--查看工资高于工号为7566员工的所有员工信息
select * from emp
where sal>(select sal from emp where empno=7566);
--2,子查询空值、多值问题
--如果子查询未返回任何行,则主查询也不会返回任何结果
select * from emp where sal>nvl((select sal from emp where empno =8888),0);
select nvl((select sal from emp where empno =8888),0) from dual;
--3,如果子查询返回单行结果,则为单行子查询,可以在主查询中对其使用相应的单行记录比较运算符
select * from emp where sal>(select sal from emp where empno=7566);
--4,如果子查询返回赴欧行结果,则为多行子查询,此时不允许对其使用单行记录比较运算符
--5,多行子查询
select * from emp where sal > any(select avg(sal) from emp group by deptno);
select * from emp where sal > all(select avg(sal) from emp group by deptno);
select * from emp where job in(select job from emp where ename = 'MARTIN' or ename = 'SMITH');
--6,TopN查询
select * from emp where rownum = 1 or rownum = 2;
select * from (select * from emp order by sal desc) where rownum <=5;
--7,分页查询
select * from (select rownum no,e.* from
(select * from emp order by sal desc )e
where rownum<=5) where no>3;
select * from (select rownum no,e.* from
(select * from emp order by sal desc) e )
where no>3 and no<=5;
--8,exists的执行流程
select * from t1 where t1 where exists (select null from t2 where y=x)
--可以理解成
for x in(select * from t1)
loop
if(exists(select null from t2 where y=x))
then
output the record
end if
end loop
--对于in和exists的性能区别:
--如果子查询独处的结果集记录较少,主要查询中格的表较大且又有索引时应该用in,
--反之若果外层的猪查询记录较少,子查询种的表大,又有索引时使用exists
--区分in和exists 主要是造成了驱动顺序的改变(这是性能变化的关键),如果是
--exists,那么以外层表为驱动表,先被访问,如果是in那么先执行子查询,所以我们会以
--驱动表的快速返回为目标,那么就会考虑到索引及结果集的关系了另外in是不对null进行处理
--如 select 1 from dual where null in (0,1,1,null);
--练习
--1,列出员工表中每个部门的员工数,和部门no
select deptno,count(*) from emp group by deptno;
--2,列出员工表中每个部门的员工数(员工数必须大于3),和部门名称
select d.*,ed.cou from dept d,
(select deptno,count(*) cou from emp group by deptno having count(*)>3) ed where d.deptno=ed.deptno;
--3,找出工资比jones高的员工
select * from emp where sal>=(select sal from emp where lower(ename)='jones')
--4,列出所有员工的姓名和其上级的姓名
select e1.ename as lower,e2.ename as upper from emp e1,emp e2 where e1.mgr=e2.empno;
select e1.ename,e1.job,e2.ename,e2.job from emp e1,emp e2 where e1.mgr=e2.empno;
--5,以职位分组,找出平均工资最高的两种职位
select * from (select job,avg(sal) a from emp group by job order by a desc) where rownum <3;
--6,查找不在部门20,且比部门20中任何一个人工资都高的员工姓名
select ename,sal from emp join (select min(sal) m from emp where deptno = 20) a on emp.deptno <> 20 and a.m<emp.sal
--7,得到平均工资大于2000的工作职种
select * from (select job,avg(sal) a from emp group by job) b where b.a>2000 order by b.a desc
--8,分部门得到工资大于2000的所有员工的平均工资,并且平均工资还要大于2500
select deptno,avg(sal),count(*) 部门工资大于2000的人数 from emp where sal >2000 group by deptno
select deptno,avg(sal) from emp group by deptno
select sal from emp where deptno = 30;
--9,得到每个月工资总数最多的那个部门的部门编号,名称,部门所在地,工资总数,部门人数
select a.d from (select deptno d,sum(sal) s,count(*) from emp group by deptno order by s desc)a where rownum<2
select * from DEPT join(select * from (select deptno d,sum(sal) s,count(8) from emp group by deptno order by s desc)a where rownum<2)b on dept.deptno = b.d;
select max(sum(sal)) from emp group by deptno
--10,分部门得到平均工资等级为2级(等级表)的部门编号
select b.dno from salgrade sa,(select deptno as dno,avg(sal) as avgsal from emp
group by deptno) b where sa.grade=4 and b.avgsal between sa.losal and sa.hisal;
--11,查找出部门10和部门20中,工资最高的第3名到工资第5名的员工的员工名字,部门名字,部门所在
select * from (select rownum no,e.* from
(select empno,sal,emp.deptno,dname,loc from emp,dept where emp.deptno=dept.deptno and emp.deptno between 10 and 20 order by emp.sal desc) e)
where no between 3 and 5;
--12,查找出收入(工资+奖金),下级比自己上级还高的员工编号,姓名,收入
select empno 工资高于上级的员工的编号,ename 工资高于上级的员工的姓名,sal+nvl(emp.comm,0) 工资高于上级的员工的总收入 from emp join (select empno eno,sal+nvl(emp.comm,0) c from emp) a
on emp.mgr=a.eno and emp.sal+nvl(emp.comm,0)>a.c
--13,查出工资等级不为4级的员工的员工名字,部门名字,部门位置
select emp.deptno,emp.ename,emp.sal from emp,salgrade where sal between losal and hisal and grade<>4
select a.en,dept.dname,dept.loc from dept
join(select emp.deptno d,emp.ename en,emp.sal es from emp,salgrade where sal between losal and hisal and grade<>4
) a on dept.deptno = a.d
--14,查找出职位和MARTIN或者SMITH一样的平均员工的平均工资
select avg(a.sal) from
(select * from emp where job =
(select job from emp where ename='MARTIN') or job = (select job from emp where ename='SMITH'))a;
select avg(sal) from emp where job in(select job from emp where ename = 'MARTIN' or ename = 'SMITH');
select avg(sal) from emp where job in(select job from emp where ename in('MARTIN','SMITH'));
--15,查找不属于任何部门的员工
select * from emp where deptno is null or deptno not in(select deptno from dept);
--16,按部门统计员工数,查出员工数最多的部门中工资第二名到第五名的所有员工信息
select * from(select rownum N,e.* from (select emp.* from emp where deptno =
(select deptno from (select deptno,count(*) c from emp group by deptno order by c desc) where rownum =1)
order by sal desc)e) where N between 2 and 5;
--17,查出king所在部门的部门号\部门名称\部门人数
select a.deptno,dname,a.c from dept join
(select deptno,count(*) c from emp where emp.deptno=(select deptno from emp where ename = 'KING')group by deptno) a on
dept.deptno = a.deptno
--18,查出king所在部门工作年限最大的员工名字
select * from emp where hiredate = (select min(hiredate) from emp where emp.deptno = (select deptno from emp where ename='KING'));
--19,查出工资成本最高的部门的部门编号和部门名称
select emp.deptno,dept.dname from emp,dept
where nvl(emp.mgr,0)+sal = (select max(nvl(emp.mgr,0)+sal) from emp)
and emp.deptno = dept.deptno;
oracle 子查询 经典总结
最新推荐文章于 2018-08-30 09:24:30 发布