17/12/6 子查询

单行子查询

括号内的查询叫做子查询，也叫内部查询，先于主查询的执行。
子查询可以嵌入1.where 2.having 3.from子句中
练习1：

1.查询入职日期最早的员工姓名，入职日期。

select ename, hiredate
from emp
where hiredate = (SELECT min(hiredate)
                  from EMP)

2.查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名，工资，部门名称。

select ename, sal, dept.dname 
from emp
join dept on emp.deptno = dept.deptno
where sal > (select sal
             from emp
             where ename = 'SMITH')
and dept.loc = 'CHICAGO'

3.查询入职日期比20部门入职日期最早的员工还要早的员工姓名，入职日期。

select ename, hiredate
from emp
where hiredate < (select min(hiredate)
                  from emp
                  where deptno = 20)

4.查询部门人数大于所有部门平均人数的部门编号，部门名称，部门人数。

select emp.deptno, dname, count(*)
from emp
join dept on dept.deptno = emp.deptno
group by emp.deptno, dname
HAVING count(*) > (select avg(count(*))
                           from emp
                           group by emp.deptno)

多行子查询

-子查询返回的条数，可以是一套或多条
-和多行子查询进行比较时，需要使用多行操作符.
in:
any: 表示和子查询的任意一行结果进行比较，有一个满足条件即可。
<li>< any: 表示小于子查询结果集中的任意一个，即小于最大值就可以。
<li>> any: 反之，大于最小值即可。
<li>= any: 相当于IN。
all: 表示和子查询的所有行记过进行比较，每一行必须满足条件。
<li>< all: 表示小于子查询结果集中的所有行，即小于最小值。
<li>>all: 反之，大于最大值。
<li>=all: 无意义。

练习2
1.查询入职日期比10部门任意一个员工晚的员工姓名、入职日期，不包括10部门员工。

select ename, hiredate
from emp
where hiredate >any (select hiredate
                     from emp
                     where deptno=10)
and deptno <> 10

2.查询入职日期比10部门任意一个员工晚的员工姓名、入职日期，不包括10部门的员工。

select ename, hiredate
from emp
where hiredate >all (select hiredate
                     from emp
                     where deptno=10)
and deptno <> 10

3.查询职位和10部门任意一个员工职位相同的员工姓名，职位，不包括10部门员工。

select ename, job
from emp
where job in (select job
              from emp
              where deptno=10)
and deptno <> 10

多列子查询

-之前讲的子查询都是在一个条件表达式内和子查询的一个列进行比较，多列子查询可以在一个条件表达式内同时和子查询的多个列进行比较。
-多列子查询通常用IN操作符完成。

练习3
1.查询职位及经理和10部门任意一个员工职位及经理相同的员工姓名，职位，不包括10部门员工。

select ename, job
from emp
where (job, mgr) in (select job,mgr
                     from emp
                     where deptno=10)
and deptno <> 10

2.查询职位及经理和10部门任意一个员工职位或经理相同的员工姓名，职位，不包括10部门员工。

select ename, job
from emp
where (job in (select job
              from emp
              where deptno=10)
or MGR IN(select mgr
              from emp
              where deptno=10))
and deptno<>10

子查询中的空值

无论什么时候只要空值有可能成为子查询结果的一部分，就不能使用NOT IN运算符。

在from子句中使用子查询

查询比自己部门平均工资高的员工姓名，工资，部门编号，部门平均工资。

select a.ename, a.sal, a.deptno, b.salavg
from emp a, (select deptno, avg(sal) salavg
             from emp
             group by deptno) b
where a.deptno = b.deptno
and a.sal > b.salavg

练习4：
1.查询比自己职位平均工资高的员工姓名、职位，部门名称，职位平均工资。

select a.ename, a.job, dept.deptno, b.salavg
from emp a, dept, (select job, avg(sal) salavg
             from emp 
             group by job) b
where a.job = b.job and a.DEPTNO = DEPT.DEPTNO
and a.sal > b.salavg

2.查询职位和经理同员工SCOTT或BLACK完全相同的员工姓名、职位、不包括SCOTT和BLAKE本人。
方法1：多列子查询

select ename, job
from emp 
where (job, mgr) in (
select job, mgr 
from emp 
 where ename  in('SCOTT','BLAKE') )
and ename not in ('SCOTT','BLAKE')

方法2：建立一个与'SCOTT' ，'BLAKE'的职位和经理的临时表，然后与emp建立连接，过滤掉与SCOTT 与 BLAKE 职位经理不同的行。

select ename,EMP.job
from EMP
join 
(select job,mgr
from emp 
where ename in ('SCOTT','BLAKE')) tempTab 
on EMP.job = TEMPTAB.job and EMP.MGR = TEMPTAB.mgr
where ename not in ('SCOTT','BLAKE')

ROWNUM(伪列)

TOP-N查询

练习5
1.查询入职日期最早的前5名员工姓名，入职日期

SELECT ROWNUM, ename, hiredate
FROM(SELECT ename,hiredate
from emp
ORDER BY hiredate) demptab
where ROWNUM < =2

2.查询工作在CHICAGO并且入职日期最早的前两名员工姓名，入职日期。

SELECT ROWNUM, ename, hiredate
FROM(SELECT ename,hiredate,deptno
from emp
ORDER BY hiredate) demptab
join dept on dept.deptno = DEMPTAB.DEPTNO
where ROWNUM < =2 and dept.loc = 'CHICAGO'

分页

练习7
1.按照每页显示5条记录，分别查询工资最高的第一页，第二页，第三页信息，要求显示员工姓名、入职日期、部门名称、工资。
方法1：
第一页

select *
from (select rownum rn, *
       from emp join dept on emp.deptno= dept.deptno
       order by sal desc) b
where rn>0 and rn <=5

方法2：

select tempTab2.ENAME,tempTab2.HIREDATE,DEPT.DNAME,TEMPTAB2.sal
from 
(select rownum rn, tempTab.* from (select * from emp order by sal desc) tempTab where rownum <= 15) tempTab2
join dept on DEPT.deptno = tempTab2.deptno
where tempTab2.rn > 10

第二页，第三页。。。

课后作业
1.查询工资高于编号为7782的员工工资，并且和7369号员工从事相同工作的员工的编号、姓名及工资。

select empno, ename, sal
from emp
where sal>(select sal 
       from emp
       where empno=7782) and 
       job = (select job 
              from emp
              where empno=7369)

2.查询工资最高的员工姓名和工资。

select ename, sal
from emp
where sal = (select max(sal)
             from emp)

3.查询部门最低工资高于10号部门最低工资的部门的编号、名称及部门最低工资。

select EMP.deptno, DEPT.dname, min(sal)
from emp
join dept on emp.deptno = dept.deptno
group by EMP.deptno, dept.dname
having min(sal) > (select min(sal)
                   from emp 
                   where deptno=10)

4.查询员工工资为其部门最低工资的员工的编号和姓名及工资。

select empno, ename, sal
from emp
join dept on emp.deptno = dept.deptno
where sal in (select min(sal)
             from emp
             group by deptno )

5.显示经理是KING的员工姓名，工资。

select a.ename, a.sal
from emp a
join emp b on a.mgr = b.empno
where b.ename = 'KING'

6.显示比员工SMITH参加工作时间晚的员工姓名，工资，参加工作时间。

select ename, sal, hiredate
from emp
where hiredate > (select hiredate
                  from emp
                  where ename = 'SMITH')

7.使用子查询的方式查询那些职员在NEW YORK工作。

select ename
from emp
join dept on emp.deptno = dept.deptno
where loc = 'NEW YORK'

8.写一个查询显示和员工SMITH工作在同一个部门的员工姓名，雇佣日期，查询结果中排除SMITH。

select ename, hiredate
from emp
where deptno = (select deptno
                from emp
                where ename='SMITH') 
                and  ename <> 'SMITH'

9.写一个查询显示其工资比全体职员平均工资高的员工编号、姓名。

select empno, ename
from emp
where sal > (select avg(sal)
             from emp)

10.写一个查询显示其上级领导是King的员工姓名、工资。

select a.ename, a.sal
from emp a
join emp b on a.mgr = b.empno
where a.mgr = (select empno
               from emp
               where ename = 'KING')

11.显示所有工作在RESEARCH部门的员工姓名，职位。

select ename, job
from emp
join dept on emp.deptno = dept.deptno
where dept.dname = 'RESEARCH'

12.查询每个部门的部门编号、平均工资，要求部门的平均工资高于部门20的平均工资。

select deptno, avg(sal) 
from emp
group by deptno
where avg(sal) > (select avg(sal)
                  from emp
                  group by deptno)

13.查询大于自己部门平均工资的员工姓名，工资，所在部门平均工资，高于部门平均工资的额度。

select e.ename, e.sal, b.avgsal, e.sal-b.avgsal  
from emp e
join(select deptno,avg(sal) avgsal
from emp
group by deptno) b on e.deptno = b.deptno
where e.sal>b.avgsal

注：b.avg(sal) 必须得起别名。
14.列出至少有一个雇员的所有部门。

select deptno, count(*)
from emp
group by deptno

15.列出薪金比"SMITH"多的所有雇员.

select ename
from emp
where sal > (select sal
             from emp
             WHERE ename = 'SMITH')

16.列出入职日期早于其直接上级的所有雇员.

select
from emp woker
join emp manager on woker.mgr = manager

17.找员工姓名和直接上级的名字。

select woker.ename , manager.ename
from emp woker
join emp manager on woker.mgr = manager.empno

18.显示部门名称和人数

select dname, nvl(count(empno),0)
from emp
right join dept on emp.deptno = dept.deptno
group by dname

19.显示每个部门的最高工资的员工
方法1：

SELECT * 
FROM EMP 
WHERE (DEPTNO,SAL) IN (SELECT DEPTNO,MAX(SAL) FROM EMP GROUP BY DEPTNO)

方法2：

select *
from emp a
join(select deptno, max(sal) maxsal
from emp 
group by deptno) b on a.deptno = b.deptno and a.sal = b.maxsal

20.显示出和员工号7369部门相同的员工姓名，工资

select ename, sal
from emp
where deptno = (select deptno 
                from emp
                where empno = 7369) and empno <> 7369

21.显示出和姓名中包含"W"的员工相同部门的员工姓名

select ename
from emp
where deptno = (select deptno
                from emp
                where ename like '%W%' )

22.显示出工资大于平均工资的员工姓名，工资

select ename ,sal
from emp
where sal > (select avg(sal)
             from emp  )

23.显示出工资大于本部门平均工资的员工姓名工资

select ename, sal
from emp a
join(select DEPTNO, avg(sal) avgsal
     from emp
     group by deptno) b
on a.deptno = b.deptno
where a.sal > b.avgsal

24.显示每位经理管理员工的最低工资，及最低工资者的姓名。
方法1：

select EMP.ename,EMP.sal
 from EMP
 join (select EMP.mgr, min(sal) minsal
 from EMP
 GROUP BY EMP.mgr) tempTab on EMP.mgr = tempTab.mgr and EMP.sal = tempTab.minsal

方法2：

select sal, ename
from emp
where (mgr, sal) in (select mgr, min(sal)
                    from emp
                    group by mgr)

25.显示比工资最高的员工参加工作时间晚的员工姓名，参加
工作时间

select ename, HIREDATE
from emp
where  hiredate >(select hiredate
                   from emp
                   where sal =(select max(sal) from emp))

26.显示出平均工资最高的的部门平均工资及部门名称

SELECT dname, avgsal
from dept d
join(select deptno, avg(sal) avgsal
from emp 
GROUP BY deptno) temptab on d.deptno = TEMPTAB.DEPTNO
where avgsal = (select max(avg(sal))
                 from emp
                 group by deptno)

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 194,761评论 5赞 460
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 81,953评论 2赞 371
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 141,998评论 0赞 320
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 52,248评论 1赞 263
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 61,130评论 4赞 356
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 46,145评论 1赞 272
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 36,550评论 3赞 381
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 35,236评论 0赞 253
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 39,510评论 1赞 291
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 34,601评论 2赞 310
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 36,376评论 1赞 326
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 32,247评论 3赞 313
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 37,613评论 3赞 299
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 28,911评论 0赞 17
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 30,191评论 1赞 250
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 41,532评论 2赞 342
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 40,739评论 2赞 335

17/12/6 子查询

17/12/6 子查询

单行子查询

多行子查询

多列子查询

子查询中的空值

在from子句中使用子查询

ROWNUM(伪列)

TOP-N查询

分页

推荐阅读更多精彩内容