请免费分享！严禁谋利！ Oracle学习笔记
常彦博
select distinct a2.id,a2.real_name from account a1 join account a2 on a1.recommender_id = a2.id; 10）其他案例
eg1：显示客户姓名，开通的远程登录业务的数量。(结果集中只包含开通了远程登
录业务的客户) select t1.real_name,t2.cnt from account t1 join (select account_id,count(*) cnt from service group by account_id) t2 on t2.account_id=t1.id; ? 注意事项：count(*)已经到了不得不起别名的地步，组函数不可作与单行函数在一起显示的。
eg2：显示客户姓名，开通的远程登录业务的数量。(结果集中只包含开通了远程登录业务的客户) select min(a.real_name),count(s.account_id) from account a join service s on a.id=s.account_id group by a.id; ? 注意事项： ? eg1比eg2的效率高！两个表出统计结果时，一个表就能出来结果的就先单表统计再连接。否则，就先连接再统计！！！！ ? in（非关联子查询） exists（关联子查询） join（表查询） 都是在解决匹配问题。 ? 匹配是记录和记录的匹配，是逻辑上的匹配，不一定非要是物理上独立个体的匹配，也可在一个表的记录间相互匹配。 13.5外连接 作用：①把匹配和不匹配的都找出来。②只找不匹配的，匹配的交给内连接作。 1）语法：left right full定驱动表的 from t1 left （outer） join t2 on t1.c1=t2.c2 outer 可省 左表为驱动表 from t1 right（outer） join t2 on t1.c1=t2.c2 右表为驱动表 from t1 full （outer） join t2 on t1.c1=t2.c2 左右表都为驱动表 2）外连接原理一： t1和t2表作外连接，连接条件为from t1 left outer join t2 on t1.c1=t2.c2，t1表必须作驱动表，t2表作匹配表，记录的匹配过程如下： ①从t1表中读取一条记r1，若它的列c1值为1 ②根据该值到t2表中查找匹配的记录，即需要遍历t2表，从t2表中的第一条记录开始，若查找的记录的c2列的值为1，我们就说这两条记录能够匹配上，那么t1的r1和t2中刚刚匹配的该条记录组合起来，作为结果集里的一条记录，否则检测t2表中的下一条记录。
③按照步骤2依次将t2表中所有的记录检测一遍，只要匹配就放入结果集中。若扫
描完后，t1的r1记录在t2表中找不到任何匹配的记录，t2表中模拟一条null记录与t1表中的r1组合起来，放入结果集中。
④从t1表中读取第二条记录，依次重复步骤2和3，产生最终的结果集。 3）外连接原理二：
t1和t2表作外连接，连接条件为from t1 left outer join t2 on t1.c1=t2.c2，t1表必须
作驱动表，t2表作匹配表：
①外连接的结果集＝内连接结果集+t1表中匹配不上的记录和一条null记录（按t2
表的结构）组成的记录的组合。
25
请免费分享！严禁谋利！ Oracle学习笔记
常彦博
②外连接的核心可以将t1中匹配不上的记录（按on条件在t2中找不到对应的匹配
记录）也显示出来，而不像内连接直接过滤掉，即t1中的记录一个都不少的出现在结果集中。
③外连接结果集的记录数不一定是驱动表的记录数（结果集记录数>=驱动表记录
数）。
4）外连接原理三：
t1和t2表作外连接，连接条件为from t1 right outer join t2 on t1.c1=t2.c2，t2表必须
作驱动表，t1表作匹配表：
①外连接的结果集＝内连接结果集+t2表中匹配不上的记录和一条null记录（按t1
表的结构）组成的记录的组合。
②外连接的核心可以将t2中匹配不上的记录（按on条件在t1中找不到对应的匹配
记录）也显示出来，而不像内连接直接过滤掉，即t2中的记录一个都不少的出现在结果集中。 ③外连接结果集的记录数不一定是驱动表的记录数（结果集记录数>=驱动表记录数）。 5）外连接原理四： t1和t2表作外连接，连接条件为from t1 full outer join t2 on t1.c1=t2.c2，t1表必须作驱动表，t2表作匹配表： ①外连接的结果集＝内连接结果集+t1表中匹配不上的记录和一条null记录（按t2表的结构）组成的记录+t2表中匹配不上的记录和一条null记录（按t1表的结构）组成的记录的组合。 ②外连接结果集的记录数不一定是t1表和t2表的记录数之和。 eg1：列出客户姓名以及他的推荐人 select a1.real_name customer,nvl(a2.real_name,'No Recommender') recommender from account a1 left join account a2 on a1.recommender_id = a2.id; eg2：列出客户姓名以及所开通的远程登录业务的信息 (没有申请远程登录业务的客户也要出现在结果集中) select a.id,a.real_name,s.unix_host,s.os_username from account a left join service s on a.id = s.account_id; eg3：哪些客户不是推荐人 select a1.real_name recommender from account a1 left join account a2 on a1.id = a2.recommender_id where a2.id is null; 6）外连接语句的执行顺序 若on子句后面有and条件，则现对匹配表进行过滤，然后再进行外连接（join on），再对外连接的结果集用where子句进行过滤，最后用select语句生成最终的结果集。on和where后面都可以跟多个条件表达式，表达式之间用and连接
eg：哪些UNIX服务器上没有os帐号weixb select h.id,h.name,h.location from host h left join service s on h.id=s.unix_host and s.os_username='weixb' where s.id is null; ①先过滤service表，用s.os_username='weixb' ②过滤后的结果集作匹配表，host表作驱动表，进行外连接，用where对外连接的
结果集进行过滤，产生最终结果。
? 注意事项：
? 驱动表和匹配表的关系，也就是指驱动表中的记录和匹配表中的记录的关
26
请免费分享！严禁谋利！ Oracle学习笔记
常彦博
系，通过on联系；要想统计出正确的数量count，必须统计匹配表的“非空列”！
? 对内连接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较好 t2 on eg2：列出客户姓名以及他的推荐人 select t2.real_name,t1.real_name from account t1 join account 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'); 28
请免费分享！严禁谋利！ Oracle学习笔记
方式二：select name from cost where id in( select cost_id 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.name='sun-server'); eg4：哪台UNIX服务器上没有开通远程登录业务 select id from host minus select unix_host from servce; 常彦博
h.id=s.unix_host and 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