设为首页 收藏本站
查看: 979|回复: 0

[经验分享] oracle 子查询 经典总结

[复制链接]

尚未签到

发表于 2016-7-18 06:10:08 | 显示全部楼层 |阅读模式
  -----------------------------------------------子查询
--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;

运维网声明 1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表,该帖子作者与运维网享有帖子相关版权
3、所有作品的著作权均归原作者享有,请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意,请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息,一经发现立即删除。若您因此触犯法律,一切后果自负,我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责,亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏,不得用于商业或者其他用途,否则,一切后果均由您自己承担,我们对此不承担任何法律责任
7、如涉及侵犯版权等问题,请您及时通知我们,我们将立即采取措施予以解决
8、联系人Email:admin@iyunv.com 网址:www.yunweiku.com

所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其承担任何法律责任,如涉及侵犯版权等问题,请您及时通知我们,我们将立即处理,联系人Email:kefu@iyunv.com,QQ:1061981298 本贴地址:https://www.yunweiku.com/thread-245435-1-1.html 上篇帖子: Oracle中的Null值解析 下篇帖子: oracle中的表添加列
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群,最新一手资源尽在官方微信交流群!快快加入我们吧...

扫描微信二维码查看详情

客服E-mail:kefu@iyunv.com 客服QQ:1061981298


QQ群⑦:运维网交流群⑦ QQ群⑧:运维网交流群⑧ k8s群:运维网kubernetes交流群


提醒:禁止发布任何违反国家法律、法规的言论与图片等内容;本站内容均来自个人观点与网络等信息,非本站认同之观点.


本站大部分资源是网友从网上搜集分享而来,其版权均归原作者及其网站所有,我们尊重他人的合法权益,如有内容侵犯您的合法权益,请及时与我们联系进行核实删除!



合作伙伴: 青云cloud

快速回复 返回顶部 返回列表