勿传网上！严禁谋利！ Oracle学习笔记

常彦博

? 对内连接and、where用谁都行，但外连接则有严格的使用位置。 ? 过滤驱动表一定用where子句。

13.6非等值连接

不同表没有共同属性的列，但两张表的列可以写成一个SQL条件表达式。 eg1：显示客户的年龄段 select t1.real_name,round((sysdate-t1.birthdate)/365) age,t2.name from account t1 join age_segment t2 on round((sysdate-t1.birthdate)/365) between t2.lowage and t2.hiage; eg2：显示客户huangrong的年龄段 select t1.real_name,round((sysdate-t1.birthdate)/365) age,t2.name from account t1 join age_segment t2 on round((sysdate-t1.birthdate)/365) between t2.lowage and t2.hiage and real_name='huangrong'; eg3：显示青年年龄段中的客户数 select t1.real_name,round((sysdate-t1.birthdate)/365) age,t2.name from account t1 join age_segment t2 on round((sysdate-t1.birthdate)/365) between t2.lowage and t2.hiage and t2.name like'青年%'; eg4：显示各个年龄段的客户数（没有客户的年龄段的客户数为0） select max(t2.name),count(t1.id) from account t1 right join age_segment t2 on round((sysdate-t1.birthdate)/365) between t2.lowage and t2.hiage group by t2.id;搞清楚为何用t1.id统计（思考连接过程）； 若没有客户的年龄段不用出现在结果集中则采用内连接。 13.7表连接总结 1）内连接，解决匹配问题 ①等值连接：on子句后有等值条件。 ②非等值连接：不同表没有共同属性的列，但两张表的列可以写成一个SQL条件表达式。 ③自连接：同一张表，通过起别名，表达列之间的关系。 2）外连接，解决不匹配问题和表中所有记录出现在结果集 ①等值连接：on子句后有等值条件。 ②非等值连接：不同表没有共同属性的列，但两张表的列可以写成一个SQL条件表达式。

③自连接：同一张表，通过起别名，表达列之间的关系。 3）交叉连接，笛卡尔积

27

勿传网上！严禁谋利！ Oracle学习笔记

常彦博

十四、集合

14.1表连接主要解决的问题

1）两张表记录之间的匹配问题。 2）两张表记录之间的不匹配问题。 3）匹配问题+不匹配问题。

14.2集合运算

1）若将两张表看成集合，匹配问题就是集合运算中的交集。 2）若将两张表看成集合，不匹配问题就是集合运算中的差。 3）匹配问题+不匹配问题就是集合运算中的并集。 14.3集合运算符 1）union：结果集为两个查询结果的并集，是去掉重复值的，最后有自动升序。 2）union all：结果集为两个查询结果的并集，是包含重复值的，输出效果为记录升序。 3）tersect：结果集为两个查询结果的交集，不包含重复值。 4）minus：结果集为属于第一个查询的结果集，但不属于第二个查询的结果集，即从第一个查询的结果集中减去他们的交集，不包含重复值；A-B=C，A为被减数，B为减数，C为差；从A中减去和B中相同的部分。 ? 注意事项：集合运算要求两个select语句是同构的，即列的个数和数据类型必须一致。 eg1：当月包在线时长为20小时，单位费用涨5分，为40小时涨3分，其他不变（用union all实现） select base_duration,unit_cost+0.05 from cost where base_duration=20 union all select base_duration,unit_cost+0.03 from cost where base_duration=40 union all select base_duration,unit_cost from cost where base_duration not in(20,40) or base_duration is null;效率低，换成case when较好 eg2：列出客户姓名以及他的推荐人 select t2.real_name,t1.real_name from account t1 join account t2 on t1.id=t2.recommender_id union all select real_name,'No recommender' from account where recommender_id is null; eg3：sun280和sun-server上的远程登录业务使用了哪些相同的资费标准 方式一：select name from cost where id in( select cost_id from host h join service s on h.id=s.unix_host and h.name='sun280' intersect select cost_id from host h join service s on h.id=s.unix_host and h.name='sun-server'); 方式二：select name from cost where id in( select cost_id 28

勿传网上！严禁谋利！ Oracle学习笔记

常彦博

from service s where s.unix_host in ( select id from host where name='sun280') intersect select cost_id from host h join service s on h.id=s.unix_host and h.name='sun-server'); eg4：哪台UNIX服务器上没有开通远程登录业务 select id from host minus select unix_host from servce; 14.4子查询、连接、集合总结

1）匹配问题：in、exists、inner join、intersect

2）不匹配问题：not in、not exists、(outer join+where匹配表非空列 is null)、minus 3）匹配+不匹配问题：outer join、union、union all

29

勿传网上！严禁谋利！ Oracle学习笔记

常彦博

十五、排名分页问题

15.1什么是rownum

rownum是一个伪列，对查询返回的行编号即行号，由1开始依次递增。

? 注意事项：关键点：Oracle的rownum数值是在获取每行之后才赋予的！

15.2 where rownum<=5的执行过程

1）Oracle获取第一个符合条件的1行，将它叫做第一行。

2）有5行了吗？如果没有，Oracle就再返回行，因为它要满足行号小于等于5的条件。如果到了5行，那么Oracle就不再返回行。 3）Oracle获取下一行，并递增行号（从2到3再到4再到5?）。 4）返回到第2步。 15.3 where rownum=5的执行过程 1）由于Oracle没有获取到第一个符合条件的1行，即第一行。 2）所以Oracle无法获取下一行，即无法从编号为1的第一行开始递增行号（从2到3再到4再到5?）。 3）最终结果为空。 eg1：找出帐务信息表的前三条记录 select rownum,id,real_name,create_date from account where rownum <= 3; eg2：找出帐务信息表的第四条到第六条记录？ select rn,real_name from (select rownum rn,real_name from account where rownum<=6) where rn>=4; ? 注意事项：此时rownum必须有别名，否则结果将按照子查询后的表进行伪列查找，结果就为空了。 eg3：最晚开通系统的前三个客户？ select rownum,real_name,create_date from (select real_name,create_date from account order by create_date desc) where rownum<=3; ? 注意事项：要先排序后过滤，注意where、order by的执行顺序。 eg4：最晚开通系统的第四到第六名客户？ select rn,real_name,create_date from (select rownum rn,real_name,create_date from (select real_name,create_date from account order by create_date desc) where rownum<=6) where rn>=4; 30