Oracle数据库：子查询、单行子查询，多行子查询，in，any，all语句，子查询的练习案例
找工作是学历、能力和运气的超强结合体，遇到寒冬，大厂不招人，可能很多算法学生都得去找开发，测开
测开的话，你就得学数据库，sql，oracle，尤其sql要学，当然，像很多金融企业、安全机构啥的，他们必须要用oracle数据库
这oracle比sql安全，强大多了，所以你需要学习，最重要的，你要是考网络警察公务员，这玩意你不会就别去报名了，耽误时间！
oracle系列文章：
【1】Oracle数据库：啥是oracle数据库？你为啥要学oracle？
【2】Oracle数据库：oracle 11g安装教程，已安装好的oracle各个文件夹的作用，oracle用户权限怎么样
【3】Oracle数据库：oracle启动，oracle客户端工具plsql安装教程和使用方法
【4】Oracle数据库：创建表空间，创建新用户，给用户分配对象、角色和系统权限，登录新用户建表
【5】Oracle数据库：链接配置，包括sqlnet.ora里面的transnames.ora配置数据库标识符SID，listener暂时简单了解
【6】Oracle数据库：net configureation assistant工具配置监听listener，配置本地网络访问服务器上的数据库
【7】Oracle数据库：oracle字符类型、数字类型、创建表表名的命名规则
【8】Oracle数据库：约束条件：主键约束、唯一约束、检查约束、非空约束、外键约束、默认值填写
【9】Oracle数据库：表的关系：一对多，一对一，多对多，一一继承和修改的关系，表不是重点，重点是数据和约束关系
【10】Oracle数据库：sql语言结构，数据查询语言DQL，select * from table；算术，别名，连接，去重等操作
【11】Oracle数据库：约束行限制where语句，判断条件，比较条件，字符串日期格式，in,like,escape,null语句
【12】Oracle数据库：逻辑运算and，or，not和各种运算的优先级控制
【13】Oracle数据库：排序order by语句，select from where order by的执行先后顺序，各种样例
【14】Oracle数据库：oracle函数，单行函数，多行函数，upper，lower，initcap，字符串函数
【15】Oracle数据库：数字函数，日期函数，round，trunc，mod，months_between,add_months,next_day,last_day,sysdate
【16】Oracle数据库：oracle数据类型转换to_char()日期和数字转字符,to_number()字符转数字,to_date()字符转日期函数
【17】Oracle数据库：oracle函数嵌套，nvl函数，nvl2函数，nullif函数，coalesce合并函数
【18】Oracle数据库：条件表达式case when then else end，decode函数，oracle单行函数练习示例
【19】Oracle数据库：oracle多表查询，等值连接，非等值连接，自连接的sql语句和规则
【20】Oracle数据库：oracle外连接left/right/full outer join on,oracle扩展的左右外连接展示符号(+)
【21】Oracle数据库：自然连接natural join，using语句，注意避免写交叉连接
【22】Oracle数据库：oracle内连接inner join on，多表查询各种自链接、内连接、外连接的练习示例
【23】Oracle数据库：oracle组函数，聚合函数，多行函数，avg，sum，min，max，count，group by，having
【24】Oracle数据库：oracle嵌套分组函数（聚合函数），组函数的练习题，挺复杂的，用好decode函数，很有趣
文章目录
Oracle数据库：子查询、单行子查询，多行子查询，in，any，all语句，子查询的练习案例@[TOC](文章目录)oracle子查询，用得多单行子查询子查询也可以使用组函数min,max,avg,sum,count子查询的错误：多行返回，处理还是单行语句多行子查询多行子查询的in多行子查询的any多行子查询的all子查询的练习案例总结
oracle子查询，用得多
嵌套select？？？
子查询的优先级高
子查询只执行一次
看来有两类了
子查询返回结果是一个的话，对于主查询就是单行
多行子查询，结果返回多行，对于主查询就是多行
abel的薪水，返回的结果就是一个
不写子查询，看看啥情况
内连接
其他的人的表，自连接——不建议这么写，烦人
SQL> select em.last_name,em.salary from employees a,employees em where a.last_name = 'Abel' and em.salary > a.salary;LAST_NAME SALARY------------------------- ----------King 24000.00Kochhar 17000.00De Haan 17000.00Greenberg 1.00Russell 14000.00Partners13500.00Errazuriz 12000.00Ozer 11500.00Hartstein 13000.00Higgins 1.0010 rows selected
俩表
a表控制名字相同的那个链接条件
然后控制em表薪资大于a表薪资，a表薪资只看abel的
这挺烦的，这些规则确实很难记，但是就这样的没法搞
搞成子查询
先查abel的薪水
SQL> select em.last_name, em.salary from employees em where em.salary > (select m.salary from employees m where m.last_name = 'Abel'); LAST_NAME SALARY------------------------- ----------King 24000.00Kochhar 17000.00De Haan 17000.00Greenberg 1.00Russell 14000.00Partners13500.00Errazuriz 12000.00Ozer 11500.00Hartstein 13000.00Higgins 1.0010 rows selected
就通过子查询联合搞进去就OK了
子查询更符合人类的思维 舒服 子查询更符合人类的思维 舒服 子查询更符合人类的思维 舒服
单行子查询
雇员id是141的jobid是多少？
SQL> select job_id from employees e where e.employee_id = 141;JOB_ID----------ST_CLERK
那那些与这个id相同的人名和id是
SQL> select em.last_name,em.job_id from employees em where em.job_id = (select e.job_id from employees e where e.employee_id = 141);LAST_NAME JOB_ID------------------------- ----------Nayer ST_CLERKMikkilineniST_CLERKLandryST_CLERKMarkleST_CLERKBissotST_CLERKAtkinson ST_CLERKMarlowST_CLERKOlson ST_CLERKMallinST_CLERKRogersST_CLERKGee ST_CLERKPhiltankerST_CLERKLadwigST_CLERKStilesST_CLERKSeo ST_CLERKPatel ST_CLERKRajs ST_CLERKDaviesST_CLERKMatos ST_CLERKVargasST_CLERK20 rows selected
job_id相同的那些人
查出来了
好说
判断条件要搞对
多了薪水，好说
SQL> select em.last_name,em.job_id from employees em where em.job_id = (select e.job_id from employees e where e.employee_id = 141) and em.salary > (select e.salary from employees e where e.employee_id = 143);LAST_NAME JOB_ID------------------------- ----------Nayer ST_CLERKMikkilineniST_CLERKBissotST_CLERKAtkinson ST_CLERKMallinST_CLERKRogersST_CLERKLadwigST_CLERKStilesST_CLERKSeo ST_CLERKRajs ST_CLERKDaviesST_CLERK11 rows selectedSQL> select em.last_name,em.job_id,em.salary from employees em where em.job_id = (select e.job_id from employees e where e.employee_id = 141) and em.salary > (select e.salary from employees e where e.employee_id = 143);LAST_NAME JOB_ID SALARY------------------------- ---------- ----------Nayer ST_CLERK3200.00MikkilineniST_CLERK2700.00BissotST_CLERK3300.00Atkinson ST_CLERK2800.00MallinST_CLERK3300.00RogersST_CLERK2900.00LadwigST_CLERK3600.00StilesST_CLERK3200.00Seo ST_CLERK2700.00Rajs ST_CLERK3500.00DaviesST_CLERK3100.0011 rows selected
就是多一个比较条件就行了
子查询
sql语句是真的骚，挺多，听冗杂的
可以包含多个子查询，且关系
子查询也可以使用组函数min,max,avg,sum,count
好说
SQL> select min(salary) min from employees e;MIN----------2100
最低薪水
然后查
SQL> select em.last_name, em.job_id, em.salary from employees em where em.salary = (select min(salary) min from employees e);LAST_NAME JOB_ID SALARY------------------------- ---------- ----------Olson ST_CLERK2100.00
这样的话，子查询中就有一个min的组函数了
having是过滤条件，这里面也可以用子查询，反正就是搞一个结果而已
就是java中的子函数
没啥稀奇
差部门id为50的人最低薪水是？
SQL> select min(e.salary) from employees e where e.department_id = 50;MIN(E.SALARY)-------------2100
然后看那些部门的最低薪水，比这个还小
那就是按照部门分组
SQL> select em.department_id, min(em.salary) from employees em group by em.department_id having min(em.salary) > (select min(e.salary) from employees e where e.department_id = 50);DEPARTMENT_ID MIN(EM.SALARY)------------- --------------100 690030 25007000901700020 60007010000110 830080 610040 650060 420010 440011 rows selected
这就是按部门划分，然后找部门里面最低的薪水，这个最低，还要大于50号部门最低薪水的值
骚
子查询的错误：多行返回，处理还是单行语句
当多行返回结果来了，你用单行比较运算，是不行的
多行子查询
多个行，那就是很多的比较了
比如
涉及到跟多个结果的对比
in是比较等于
而any和all是大于和小于
多行子查询的in
部门分组，找部门最低，然后显示他们的名字
首先找每个部门的最低薪水，那很多部门分组，的话，回来的结果很多个结果
SQL> select min(em.salary) from employees em group by em.department_id;MIN(EM.SALARY)--------------690025007000170006000100008300210061006500440012 rows selected
12个部门
每个人的薪水=这些最低值
展示
SQL> select e.last_name,e.job_id,e.salary from employees e where e.salary = (select min(em.salary) from employees em group by em.department_id);select e.last_name,e.job_id,e.salary from employees e where e.salary = (select min(em.salary) from employees em group by em.department_id)ORA-01427: 单行子查询返回多个行
你要是用=
报错了
把=改为in
才是多行子查询返回结果的正确姿势
SQL> select e.last_name,e.job_id,e.salary from employees e where e.salary in (select min(em.salary) from employees em group by em.department_id);LAST_NAME JOB_ID SALARY------------------------- ---------- ----------Kochhar AD_VP 17000.00De Haan AD_VP 17000.00Ernst IT_PROG 6000.00Lorentz IT_PROG 4200.00Popp FI_ACCOUNT 6900.00ColmenaresPU_CLERK2500.00Vollman ST_MAN 6500.00MarlowST_CLERK2500.00Olson ST_CLERK2100.00Patel ST_CLERK2500.00VargasST_CLERK2500.00TuckerSA_REP 10000.00Tuvault SA_REP 7000.00King SA_REP 10000.00SewallSA_REP 7000.00Bloom SA_REP 10000.00Kumar SA_REP 6100.00Grant SA_REP 7000.00Sullivan SH_CLERK2500.00Sarchand SH_CLERK4200.00LAST_NAME JOB_ID SALARY------------------------- ---------- ----------Perkins SH_CLERK2500.00WhalenAD_ASST 4400.00Fay MK_REP 6000.00MavrisHR_REP 6500.00Baer PR_REP 10000.00Gietz AC_ACCOUNT 8300.0026 rows selected
看到了吗，这些都是薪水最差劲的员工
gg
这就是多行子查询
多行子查询的any
满足其一条件即可
美滋滋
小于<(100,200,300)
或者
大于>(100,200,300)
只要满足一个就好了
查询不是it的所有员工
他们工资小于it的薪水
子查询是it的薪水那些员工们
不止一个人在这个岗位上，懂吧？？多行返回的
SQL> select em.salary from employees em where em.job_id='IT_PROG';SALARY----------9000.006000.004800.004800.004200.00
然后，其他的员工中，薪水小于这里面的都展示一下，其实就是小于9000的
SQL> select e.last_name,e.job_id,e.salary from employees e where e.job_id !='IT_PROG' and e.salary < any(select em.salary from employees em where em.job_id='IT_PROG');LAST_NAME JOB_ID SALARY------------------------- ---------- ----------Olson ST_CLERK2100.00PhiltankerST_CLERK2200.00MarkleST_CLERK2200.00Gee ST_CLERK2400.00LandryST_CLERK2400.00Perkins SH_CLERK2500.00Sullivan SH_CLERK2500.00VargasST_CLERK2500.00Patel ST_CLERK2500.00MarlowST_CLERK2500.00ColmenaresPU_CLERK2500.00HimuroPU_CLERK2600.00OConnell SH_CLERK2600.00Grant SH_CLERK2600.00Matos ST_CLERK2600.00MikkilineniST_CLERK2700.00Seo ST_CLERK2700.00TobiasPU_CLERK2800.00Geoni SH_CLERK2800.00Atkinson ST_CLERK2800.00LAST_NAME JOB_ID SALARY------------------------- ---------- ----------Jones SH_CLERK2800.00Gates SH_CLERK2900.00Baida PU_CLERK2900.00RogersST_CLERK2900.00FeeneySH_CLERK3000.00CabrioSH_CLERK3000.00Khoo PU_CLERK3100.00Walsh SH_CLERK3100.00DaviesST_CLERK3100.00FleaurSH_CLERK3100.00McCainSH_CLERK3200.00StilesST_CLERK3200.00Nayer ST_CLERK3200.00TaylorSH_CLERK3200.00BissotST_CLERK3300.00MallinST_CLERK3300.00Dellinger SH_CLERK3400.00Rajs ST_CLERK3500.00LadwigST_CLERK3600.00Dilly SH_CLERK3600.00Chung SH_CLERK3800.00LAST_NAME JOB_ID SALARY------------------------- ---------- ----------Everett SH_CLERK3900.00Bell SH_CLERK4000.00Bull SH_CLERK4100.00Sarchand SH_CLERK4200.00WhalenAD_ASST 4400.00Mourgos ST_MAN 5800.00Fay MK_REP 6000.00Kumar SA_REP 6100.00Johnson SA_REP 6200.00Banda SA_REP 6200.00Ande SA_REP 6400.00Vollman ST_MAN 6500.00MavrisHR_REP 6500.00Lee SA_REP 6800.00Popp FI_ACCOUNT 6900.00SewallSA_REP 7000.00Grant SA_REP 7000.00Tuvault SA_REP 7000.00Marvins SA_REP 7200.00Bates SA_REP 7300.00Smith SA_REP 7400.00LAST_NAME JOB_ID SALARY------------------------- ---------- ----------Cambrault SA_REP 7500.00Doran SA_REP 7500.00Sciarra FI_ACCOUNT 7700.00Urman FI_ACCOUNT 7800.00Kaufling ST_MAN 7900.00Olsen SA_REP 8000.00Smith SA_REP 8000.00Weiss ST_MAN 8000.00Fripp ST_MAN 8200.00Chen FI_ACCOUNT 8200.00Gietz AC_ACCOUNT 8300.00LivingstonSA_REP 8400.00TaylorSA_REP 8600.00HuttonSA_REP 8800.0076 rows selected
看见any了吗
不能直接用小于<
需要<any
这样就是多行了
美滋滋
多行子查询的all
any是任意其一，or关系
而all就是全部都得满足，and关系
in是有其一=即可，也是or关系
最低薪水和薪水，不同
any是是任意其一即可
所以这里我们该any为all就是所有薪水都得比较
那自然是最低薪水了
```sqlSQL> select em.salary from employees em where em.job_id='IT_PROG';SALARY----------9000.006000.004800.004800.004200.00
因此今后你查那些人，都得小于4200
```sqlSQL> select e.last_name,e.job_id,e.salary from employees e where e.job_id !='IT_PROG' and e.salary < all(select em.salary from employees em where em.job_id='IT_PROG');LAST_NAME JOB_ID SALARY------------------------- ---------- ----------Bull SH_CLERK4100.00Bell SH_CLERK4000.00Everett SH_CLERK3900.00Chung SH_CLERK3800.00LadwigST_CLERK3600.00Dilly SH_CLERK3600.00Rajs ST_CLERK3500.00Dellinger SH_CLERK3400.00MallinST_CLERK3300.00BissotST_CLERK3300.00TaylorSH_CLERK3200.00StilesST_CLERK3200.00Nayer ST_CLERK3200.00McCainSH_CLERK3200.00Khoo PU_CLERK3100.00Walsh SH_CLERK3100.00DaviesST_CLERK3100.00FleaurSH_CLERK3100.00CabrioSH_CLERK3000.00FeeneySH_CLERK3000.00LAST_NAME JOB_ID SALARY------------------------- ---------- ----------Baida PU_CLERK2900.00Gates SH_CLERK2900.00RogersST_CLERK2900.00Geoni SH_CLERK2800.00Atkinson ST_CLERK2800.00TobiasPU_CLERK2800.00Jones SH_CLERK2800.00MikkilineniST_CLERK2700.00Seo ST_CLERK2700.00Matos ST_CLERK2600.00Grant SH_CLERK2600.00HimuroPU_CLERK2600.00OConnell SH_CLERK2600.00Patel ST_CLERK2500.00MarlowST_CLERK2500.00ColmenaresPU_CLERK2500.00Perkins SH_CLERK2500.00Sullivan SH_CLERK2500.00VargasST_CLERK2500.00LandryST_CLERK2400.00Gee ST_CLERK2400.00LAST_NAME JOB_ID SALARY------------------------- ---------- ----------MarkleST_CLERK2200.00PhiltankerST_CLERK2200.00Olson ST_CLERK2100.0044 rows selected
区别就是把上面一个案例的any改为all了
你瞅瞅就知道
舒服吧
子查询的练习案例
先看z的部门是啥
子查询
SQL> select e.department_id from employees e where e.last_name = 'Zlotkey';DEPARTMENT_ID-------------80
然后找，其他人在这80部门的别人
SQL> select em.last_name,em.department_id from employees em where em.department_id = (select e.department_id from employees e where e.last_name = 'Zlotkey') and em.last_name != 'Zlotkey';LAST_NAME DEPARTMENT_ID------------------------- -------------Russell80Partners 80Errazuriz 80Cambrault 80Tucker 80Bernstein 80Hall 80Olsen 80Cambrault 80Tuvault80King 80Sully 80McEwen 80Smith 80Doran 80Sewall 80Vishney80Greene 80Marvins80Lee 80LAST_NAME DEPARTMENT_ID------------------------- -------------Ande 80Banda 80Ozer 80Bloom 80Fox 80Smith 80Bates 80Kumar 80Abel 80Hutton 80Taylor 80Livingston 80Johnson8033 rows selected
好说
平均薪水是子查询
好说
SQL> select em.last_name,em.salary from employees em where em.salary > (select avg(e.salary) from employees e);LAST_NAME SALARY------------------------- ----------King 24000.00Kochhar 17000.00De Haan 17000.00Hunold 9000.00Greenberg 1.00Faviet 9000.00Chen8200.00Sciarra 7700.00Urman 7800.00Popp6900.00Raphaely11000.00Weiss 8000.00Fripp 8200.00Kaufling 7900.00Vollman 6500.00Russell 14000.00Partners13500.00Errazuriz 12000.00Cambrault 11000.00Zlotkey 10500.00LAST_NAME SALARY------------------------- ----------Tucker 10000.00Bernstein9500.00Hall9000.00Olsen 8000.00Cambrault7500.00Tuvault 7000.00King 10000.00Sully 9500.00McEwen 9000.00Smith 8000.00Doran 7500.00Sewall 7000.00Vishney 10500.00Greene 9500.00Marvins 7200.00Lee6800.00Ozer 11500.00Bloom 10000.00Fox9600.00Smith 7400.00Bates 7300.00LAST_NAME SALARY------------------------- ----------Abel 11000.00Hutton 8800.00Taylor 8600.00Livingston 8400.00Grant 7000.00Hartstein 13000.00Mavris 6500.00Baer 10000.00Higgins 1.00Gietz 8300.0051 rows selected
好说
SQL> select em.last_name,em.job_id from employees em where em.salary > (select avg(e.salary) from employees e) order by em.salary;LAST_NAME JOB_ID------------------------- ----------MavrisHR_REPVollman ST_MANLee SA_REPPopp FI_ACCOUNTTuvault SA_REPSewallSA_REPGrant SA_REPMarvins SA_REPBates SA_REPSmith SA_REPCambrault SA_REPDoran SA_REPSciarra FI_ACCOUNTUrman FI_ACCOUNTKaufling ST_MANWeiss ST_MANSmith SA_REPOlsen SA_REPFripp ST_MANChen FI_ACCOUNTLAST_NAME JOB_ID------------------------- ----------Gietz AC_ACCOUNTLivingstonSA_REPTaylorSA_REPHuttonSA_REPMcEwenSA_REPHall SA_REPFavietFI_ACCOUNTHunoldIT_PROGSully SA_REPBernstein SA_REPGreeneSA_REPFox SA_REPKing SA_REPTuckerSA_REPBaer PR_REPBloom SA_REPZlotkey SA_MANVishney SA_REPAbel SA_REPCambrault SA_MANRaphaely PU_MANLAST_NAME JOB_ID------------------------- ----------Ozer SA_REPErrazuriz SA_MANGreenberg FI_MGRHiggins AC_MGRHartstein MK_MANPartners SA_MANRussell SA_MANDe Haan AD_VPKochhar AD_VPKing AD_PRES51 rows selected
排序好
很多部门应该有u的名字
SQL> select e.department_id from employees e where e.last_name like '%u%';DEPARTMENT_ID-------------60603050508080808080808080805050505018 rows selected
这些部门都有这种人
SQL> select em.employee_id,em.last_name from employees em where em.department_id in (select e.department_id from employees e where e.last_name like '%u%');EMPLOYEE_ID LAST_NAME----------- -------------------------107 Lorentz106 Pataballa105 Austin
很多
departments表查部门id
还有employee表，根据部门id查e表里面的列
1–返回部门id
SQL> select d.department_id from departments d where d.location_id = 1700;DEPARTMENT_ID-------------1030901001101014015016017018019020021020240250260DEPARTMENT_ID-------------27021 rows selected
很多部门，显然是多表子查询，用in
再看e表
SQL> select e.last_name,e.department_id,e.job_id from employees e where e.department_id in (select d.department_id from departments d where d.location_id = 1700);LAST_NAME DEPARTMENT_ID JOB_ID------------------------- ------------- ----------King 90 AD_PRESKochhar90 AD_VPDe Haan90 AD_VPGreenberg 100 FI_MGRFaviet100 FI_ACCOUNTChen 100 FI_ACCOUNTSciarra 100 FI_ACCOUNTUrman 100 FI_ACCOUNTPopp 100 FI_ACCOUNTRaphaely 30 PU_MANKhoo 30 PU_CLERKBaida 30 PU_CLERKTobias 30 PU_CLERKHimuro 30 PU_CLERKColmenares 30 PU_CLERKWhalen 10 AD_ASSTHiggins 110 AC_MGRGietz 110 AC_ACCOUNT18 rows selected
好说，in语句搞定
king的雇员id，看看谁的manager_id是king
SQL> select e.employee_id from employees e where e.last_name = 'King';EMPLOYEE_ID-----------156100
俩人
谁向他们汇报嗯【他们就是经理】
in
SQL> select em.last_name from employees em where em.manager_id in (select e.employee_id from employees e where e.last_name = 'King');LAST_NAME-------------------------KochharDe HaanRaphaelyWeissFrippKauflingVollmanMourgosRussellPartnersErrazurizCambraultZlotkeyHartstein14 rows selected
看看部门ex的部门id是啥呢
这在departments表里面
然后才能调用employees表
SQL> select d.department_id from departments d where d.department_name = 'Executive';DEPARTMENT_ID-------------90
貌似就一个结果
可能会有多个结果
SQL> select e.department_id,e.last_name,e.job_id from employees e where e.department_id in (select d.department_id from departments d where d.department_name = 'Executive');DEPARTMENT_ID LAST_NAME JOB_ID------------- ------------------------- ----------90 King AD_PRES90 Kochhar AD_VP90 De Haan AD_VPSQL> select e.department_id,e.last_name,e.job_id from employees e where e.department_id = (select d.department_id from departments d where d.department_name = 'Executive');DEPARTMENT_ID LAST_NAME JOB_ID------------- ------------------------- ----------90 King AD_PRES90 Kochhar AD_VP90 De Haan AD_VP
2种方法都行
只要结果是一行，可以当多行用
子查询是看u名字的部门
另外薪水还得高于平均薪水
俩子查询
SQL> select avg(salary) from employees;AVG(SALARY)-----------6461.831775SQL> select em.department_id from employees em where em.last_name like '%u%';DEPARTMENT_ID-------------60603050508080808080808080805050505018 rows selected
主查询联合俩条件
SQL> select e.last_name, e.department_id, e.job_id from employees e where e.department_id in(select em.department_id from employees em where em.last_name like '%u%') and e.salary > (select avg(salary) from employees);LAST_NAME DEPARTMENT_ID JOB_ID------------------------- ------------- ----------Hunold 60 IT_PROGVollman50 ST_MANKaufling 50 ST_MANFripp 50 ST_MANWeiss 50 ST_MANLivingston 80 SA_REPTaylor 80 SA_REPHutton 80 SA_REPAbel 80 SA_REPBates 80 SA_REPSmith 80 SA_REPFox 80 SA_REPBloom 80 SA_REPOzer 80 SA_REPLee 80 SA_REPMarvins80 SA_REPGreene 80 SA_REPVishney80 SA_REPSewall 80 SA_REPDoran 80 SA_REPLAST_NAME DEPARTMENT_ID JOB_ID------------------------- ------------- ----------Smith 80 SA_REPMcEwen 80 SA_REPSully 80 SA_REPKing 80 SA_REPTuvault80 SA_REPCambrault 80 SA_REPOlsen 80 SA_REPHall 80 SA_REPBernstein 80 SA_REPTucker 80 SA_REPZlotkey80 SA_MANCambrault 80 SA_MANErrazuriz 80 SA_MANPartners 80 SA_MANRussell80 SA_MANRaphaely 30 PU_MAN36 rows selected
SQL> select e.last_name, e.salary, e.employee_id from employees e where e.department_id in(select em.department_id from employees em where em.last_name like '%u%') and e.salary > (select avg(salary) from employees);LAST_NAME SALARY EMPLOYEE_ID------------------------- ---------- -----------Hunold 9000.00 103Vollman 6500.00 123Kaufling 7900.00 122Fripp 8200.00 121Weiss 8000.00 120Livingston 8400.00 177Taylor 8600.00 176Hutton 8800.00 175Abel 11000.00 174Bates 7300.00 172Smith 7400.00 171Fox9600.00 170Bloom 10000.00 169Ozer 11500.00 168Lee6800.00 165Marvins 7200.00 164Greene 9500.00 163Vishney 10500.00 162Sewall 7000.00 161Doran 7500.00 160LAST_NAME SALARY EMPLOYEE_ID------------------------- ---------- -----------Smith 8000.00 159McEwen 9000.00 158Sully 9500.00 157King 10000.00 156Tuvault 7000.00 155Cambrault7500.00 154Olsen 8000.00 153Hall9000.00 152Bernstein9500.00 151Tucker 10000.00 150Zlotkey 10500.00 149Cambrault 11000.00 148Errazuriz 12000.00 147Partners13500.00 146Russell 14000.00 145Raphaely11000.00 11436 rows selected
其实用子查询，更符合逻辑思维
尽量不要用自连接
自连接太烦了真的
我们先分析要子查询的条件
好好学习
总结
提示：重要经验：
1）
2）学好oracle，即使经济寒冬，整个测开offer绝对不是问题！同时也是你考公网络警察的必经之路。
3）笔试求AC，可以不考虑空间复杂度，但是面试既要考虑时间复杂度最优，也要考虑空间复杂度最优。
