查看: 463|回复: 3

[每日一题] PL/SQL Challenge 每日一题:2018-9-7 分区外连接

[复制链接]
论坛徽章:
533
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
发表于 2018-9-12 07:20 | 显示全部楼层 |阅读模式

最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正),其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。

每两周的优胜者可获得itpub奖励的技术图书一本。

以往旧题索引:
http://www.itpub.net/forum.php?m ... eid&typeid=1808

原始出处:
http://www.plsqlchallenge.com/

作者: Kim Berg Hansen

运行环境:SQLPLUS, SERVEROUTPUT已打开
注:本题给出答案时候要求给予简要说明才能得到奖品

我有客户分类以及客户地区表,还有每个分类/地区的销售表:

create table qz_segments (
   segment     varchar2(10)   primary key
, description varchar2(30));

create table qz_regions (
   region      varchar2(10)   primary key
, description varchar2(30));

create table qz_sales (
   segment     varchar2(10) not null references qz_segments
, region      varchar2(10) not null references qz_regions
, amount      number);

insert into qz_segments values ('PRIVATE'    , 'Private customers');
insert into qz_segments values ('COMPANY'    , 'Companies');
insert into qz_segments values ('GOVERNMENT' , 'Public departments');

insert into qz_regions values ('AMER', 'America, North- and South-');
insert into qz_regions values ('EMEA', 'Europe, Middle-East, Africa');
insert into qz_regions values ('ASOC', 'Asia and Oceania');

insert into qz_sales values ('PRIVATE', 'AMER',  750000);
insert into qz_sales values ('PRIVATE', 'EMEA', 1150000);
insert into qz_sales values ('PRIVATE', 'ASOC',  850000);
insert into qz_sales values ('COMPANY', 'EMEA',  675000);
insert into qz_sales values ('COMPANY', 'ASOC',  515000);

commit;

我想要一张每个分类和地区的销售列表,对于我们有销售的每个分类(在sales表中存在),为regions表中的每个地区输出一行,每行数据包括:

分类(Segment), 地区(region)和销售额(如果有的话)

如果对某个分类的某个地区我们没有销售记录,则销售额为NULL

这就意味着GOVERNMENT这个分类不能出现在列表中,因为我们没有次分类的销售记录。但是对于COMPANY 和 PRIVATE这两个分类,列表中分别有三行,每个regions表中的地区对应一行——不管那个地区(对于给定分类)是否有销售记录。

根据上述规则,哪些选项包含的查询会给出一个销售列表,返回这个所需的输出:

SEGMENT    REGION         AMOUNT
---------- ---------- ----------
COMPANY    AMER
COMPANY    ASOC           515000
COMPANY    EMEA           675000
PRIVATE    AMER           750000
PRIVATE    ASOC           850000
PRIVATE    EMEA          1150000

(A)
select sal.segment, reg.region, sal.amount
  from qz_regions reg
  LEFT outer join qz_sales sal
      partition by (sal.segment)
      on sal.region  = reg.region
order by sal.segment, reg.region
/

(B)
select sal.segment, reg.region, sal.amount
  from qz_regions reg
  RIGHT outer join qz_sales sal
      partition by (sal.segment)
      on sal.region  = reg.region
order by sal.segment, reg.region
/

(C)
select sal.segment, reg.region, sal.amount
  from qz_sales sal
      partition by (sal.segment)
  LEFT outer join qz_regions reg
      on reg.region = sal.region
order by sal.segment, reg.region
/

(D)
select sal.segment, reg.region, sal.amount
  from qz_sales sal
      partition by (sal.segment)
  RIGHT outer join qz_regions reg
      on reg.region = sal.region
order by sal.segment, reg.region
/

(E)
select seg.segment, sal.region, sal.amount
  from qz_segments seg
  LEFT outer join qz_sales sal
      partition by (sal.region)
      on sal.segment  = seg.segment
order by seg.segment, sal.region
/

(F)
select seg.segment, sal.region, sal.amount
  from qz_segments seg
  RIGHT outer join qz_sales sal
      partition by (sal.region)
      on sal.segment  = seg.segment
order by seg.segment, sal.region
/

(G)
select seg.segment, sal.region, sal.amount
  from qz_sales sal
      partition by (sal.region)
  LEFT outer join qz_segments seg
      on seg.segment = sal.segment
order by seg.segment, sal.region
/

(H)
select seg.segment, sal.region, sal.amount
  from qz_sales sal
      partition by (sal.region)
  RIGHT outer join qz_segments seg
      on seg.segment = sal.segment
order by seg.segment, sal.region
/

认证徽章
论坛徽章:
47
秀才
日期:2018-11-13 15:18:40秀才
日期:2018-08-31 10:54:12秀才
日期:2018-08-31 10:53:55技术图书徽章
日期:2018-08-31 10:48:56秀才
日期:2018-08-31 10:48:48秀才
日期:2018-08-31 10:48:48秀才
日期:2018-08-31 10:48:48秀才
日期:2018-08-31 10:48:34秀才
日期:2018-08-31 10:37:20秀才
日期:2018-08-31 10:37:01
发表于 2018-9-12 10:53 | 显示全部楼层
AD
先看一下parition out join 说明
The partitioned outer join (10g) selects the partition key of the outer table even where there are no matching rows:
BC,你用   qz_sales sal 作为主要的显示表就是(那种它无论如何都是显示qz_sales 行数)  
EFGH,你用qz_segments 表不用region表肯定达不到效果


我也不知道我理解的对不对
参考:
http://www.dba-oracle.com/t_advanced_sql_full_outer_join.htm

使用道具 举报

回复
论坛徽章:
346
生肖徽章2007版:猴
日期:2008-05-16 11:28:59生肖徽章2007版:马
日期:2008-10-08 17:01:01SQL大赛参与纪念
日期:2011-04-13 12:08:17授权会员
日期:2011-06-17 16:14:53ITPUB元老
日期:2011-06-21 11:47:01ITPUB官方微博粉丝徽章
日期:2011-07-01 09:45:27ITPUB十周年纪念徽章
日期:2011-09-27 16:30:472012新春纪念徽章
日期:2012-01-04 11:51:22海蓝宝石
日期:2012-02-20 19:24:27铁扇公主
日期:2012-02-21 15:03:13
发表于 2018-9-12 16:19 | 显示全部楼层
学习了,好题!

使用道具 举报

回复
论坛徽章:
533
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
 楼主| 发表于 2018-9-13 03:12 | 显示全部楼层
答案AD, 2楼得奖。

A: 利用在sales表上的PARTITION BY子句,我们的意思是对于sales表中的每个SEGMENT值,我们想要执行这个连接。这个连接是选中所有地区(regions)然后执行对sales表的LEFT OUTER JOIN。因为这个连接发生于sales表中的每个segment, 我们得到了所要的结果。

B: PARTITION BY仍然是说我们想要为sales表中的每个SEGMENT执行连接,但是此处的连接是一个RIGHT而不是前一选项中的LEFT,这就意味着我们先找到sales数据行,然后再看看能否找到相应的regions。这样的分区外连接毫无意义,因为其结果和没有PARTITION BY子句完全一样,我们得到的是这个错误结果:

SEGMENT    REGION         AMOUNT
---------- ---------- ----------
COMPANY    ASOC           515000
COMPANY    EMEA           675000
PRIVATE    AMER           750000
PRIVATE    ASOC           850000
PRIVATE    EMEA          1150000

C: 此处我们仅仅把表的顺序对调,但是我们仍然是在sales表上执行PARTITION BY。因为我们在对调之后的表顺序上使用LEFT OUTER JOIN,这个选项和前一选项的RIGHT OUTER JOIN一样,也同样返回了毫无意义的错误结果。

D: 但是通过对调表顺序和正确放置PARTITION BY子句,此处我们的情况是RIGHT OUTER JOIN 和A选项中的 LEFT OUTER JOIN 相同,所以这个选项能正常工作,返回所需的结果。

E: 随着PARTITION BY应用到sales表,此处我们所说的是对于sales表中的每一个REGION值,我们想要执行连接。既然每个region在sales表中都出现了,我们对于所有的regions都会执行一个segment表到sales表的LEFT OUTER JOIN, 这就意味着我们会得到每个地区的GOVERNMENT, 即使我们根本就没有任何GOVERNMENT分类的销售记录。于是我们得到了这个错误结果:

SEGMENT    REGION         AMOUNT
---------- ---------- ----------
COMPANY    AMER
COMPANY    ASOC           515000
COMPANY    EMEA           675000
GOVERNMENT AMER
GOVERNMENT ASOC
GOVERNMENT EMEA
PRIVATE    AMER           750000
PRIVATE    ASOC           850000
PRIVATE    EMEA          1150000

F: 如同B选项,在一个RIGHT OUTER JOIN中,在第二个表上应用PARTITION BY毫无意义。我们得到的结果和不带PARTITION BY子句完全相同,和B选项一样是错误的。


G: 正如C选项是B选项的“反序”,并且有着同样毫无意义的问题,这也是F选项的反序版本,返回了和B,C,F同样的错误结果。


H: 最后,正如D选项是A选项的反序,这个选项是E选项的反序。但是这并没有使它变得正确,它仍然是连接到错误的表,使用了错误的分区列,这会得到和E选项相同的错误结果。

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备16024965号 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表