首页 / MYSQL / fulljoin语句练习

fulljoin语句练习

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了fulljoin语句练习，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含6052字，纯文字阅读大概需要9分钟。

内容图文

full join语句练习 需求：将表 A,B,C 合并到一个结果集中 表A如图： [html] N D 1 eeee 3 dddd 5 cccc 7 bbbb 9 aaaa 11 dddd 13 eeee 15 wwww 17 qqqq 19 tttttt 表B如图：[html] N E 5 rrrrrr 4 fffff 3 ssssss 2 jjjjjj 1 kkkkkk 7 uuuuuu 表C如图：[html

full join语句练习

需求：将表 A,B,C 合并到一个结果集中

表A如图：

[html] 
N   D  
1   eeee  
3   dddd  
5   cccc  
7   bbbb  
9   aaaa  
11  dddd  
13  eeee  
15  wwww  
17  qqqq  
19  tttttt  
表B如图：
[html] 
N   E  
5   rrrrrr  
4   fffff  
3   ssssss  
2   jjjjjj  
1   kkkkkk  
7   uuuuuu  
表C如图：
[html] 
N   F  
5   oooo  
4   lllll  
3   hhss  
2   ddfj  
1   kdsfkkk  
7   sduuu  
8   ewrtwy  
12  sdgfsd  
22  dfgee  
要得到的结果集如图：

[html] 
N   D   E   F  
1   eeee    kkkkkk  kdsfkkk  
22          dfgee  
11  dddd           
13  eeee           
2       jjjjjj  ddfj  
5   cccc    rrrrrr  oooo  
4       fffff   lllll  
17  qqqq           
8           ewrtwy  
3   dddd    ssssss  hhss  
7   bbbb    uuuuuu  sduuu  
9   aaaa           
15  wwww           
19  tttttt         
12          sdgfsd  

两种思路：
1, full join
语句为：
[html] 
select  nvl(a.n,nvl(b.n,c.n)) as N,  
            a.d,b.e,c.f  
from A full join B on A.N=B.N   
       full join c on b.n=c.n;  

2, 先union all ，再列转行
语句为：
[html] 
select n ,max(case when nn='a' then d end) as d,  
          max(case when nn='b' then d end) as e,  
          max(case when nn='c' then d end) as f  
from (  
  select n,d as d,'a' as nn from a  
  union all  
  select n,e as d,'b' as nn from b  
  union all  
  select n,f as d,'c' as nn from c  
)  
group by n;  

实际的问题是我要将70个左右的窄表连接成一个宽表，full join 几乎都编译不过，为此我对比了下2种情况的执行计划
使用 full join 的语句
[html] 
explain plan for  
select  nvl(a.n,nvl(b.n,c.n)) as N,  
            a.d,b.e,c.f  
from A full join B on A.N=B.N full join c on b.n=c.n;  
select * from table(dbms_xplan.display());  
执行
结果为 [html] Plan hash value: 2877137913 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19 | 1197 | 122 (4)| 00:00:02 | | 1 | VIEW | | 19 | 1197 | 122 (4)| 00:00:02 | | 2 | UNION-ALL | | | | | | |* 3 | HASH JOIN OUTER | | 11 | 1342 | 61 (4)| 00:00:01 | | 4 | VIEW | | 11 | 825 | 57 (2)| 00:00:01 | | 5 | UNION-ALL | | | | | | |* 6 | HASH JOIN OUTER | | 10 | 60 | 29 (4)| 00:00:01 | | 7 | TABLE ACCESS FULL| A | 10 | 30 | 14 (0)| 00:00:01 | | 8 | TABLE ACCESS FULL| B | 5 | 15 | 14 (0)| 00:00:01 | |* 9 | HASH JOIN ANTI | | 1 | 6 | 29 (4)| 00:00:01 | | 10 | TABLE ACCESS FULL| B | 5 | 15 | 14 (0)| 00:00:01 | | 11 | TABLE ACCESS FULL| A | 10 | 30 | 14 (0)| 00:00:01 | | 12 | TABLE ACCESS FULL | C | 9 | 423 | 3 (0)| 00:00:01 | |* 13 | HASH JOIN ANTI | | 8 | 272 | 61 (4)| 00:00:01 | | 14 | TABLE ACCESS FULL | C | 9 | 189 | 3 (0)| 00:00:01 | | 15 | VIEW | | 11 | 143 | 57 (2)| 00:00:01 | | 16 | UNION-ALL | | | | | | |* 17 | HASH JOIN OUTER | | 10 | 60 | 29 (4)| 00:00:01 | | 18 | TABLE ACCESS FULL| A | 10 | 30 | 14 (0)| 00:00:01 | | 19 | TABLE ACCESS FULL| B | 5 | 15 | 14 (0)| 00:00:01 | |* 20 | HASH JOIN ANTI | | 1 | 6 | 29 (4)| 00:00:01 | | 21 | TABLE ACCESS FULL| B | 5 | 15 | 14 (0)| 00:00:01 | | 22 | TABLE ACCESS FULL| A | 10 | 30 | 14 (0)| 00:00:01 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("B"."N"="C"."N"(+)) 6 - access("A"."N"="B"."N"(+)) 9 - access("A"."N"="B"."N") 13 - access("B"."N"="C"."N") 17 - access("A"."N"="B"."N"(+)) 20 - access("A"."N"="B"."N") Note ----- - dynamic sampling used for this statement 使用union all 的语句 [html] explain plan for select n ,max(case when nn='a' then d end) as d, max(case when nn='b' then d end) as e, max(case when nn='c' then d end) as f from ( select n,d as d,'a' as nn from a union all select n,e as d,'b' as nn from b union all select n,f as d,'c' as nn from c ) group by n; select * from table(dbms_xplan.display()); 执行结果为： [html] 1 Plan hash value: 1237158055 2 3 ----------------------------------------------------------------------------- 4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 5 ----------------------------------------------------------------------------- 6 | 0 | SELECT STATEMENT | | 24 | 576 | 32 (4)| 00:00:01 | 7 | 1 | HASH GROUP BY | | 24 | 576 | 32 (4)| 00:00:01 | 8 | 2 | VIEW | | 24 | 576 | 31 (0)| 00:00:01 | 9 | 3 | UNION-ALL | | | | | | 10 | 4 | TABLE ACCESS FULL| A | 10 | 30 | 14 (0)| 00:00:01 | 11 | 5 | TABLE ACCESS FULL| B | 5 | 15 | 14 (0)| 00:00:01 | 12 | 6 | TABLE ACCESS FULL| C | 9 | 189 | 3 (0)| 00:00:01 | 13 ----------------------------------------------------------------------------- 14 15 Note 16 ----- 17 - dynamic sampling used for this statement

对比2种处理方式，union all 的方式明显优于 full join。也可以看出简单的sql语句效率不一定好。

内容总结

以上是互联网集市为您收集整理的fulljoin语句练习全部内容，希望文章能够帮你解决fulljoin语句练习所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/531654.html

来源：【匿名】