查看: 590|回复: 7

[每日一题] PL/SQL Challenge 每日一题:2018-7-5 MEMBER OF

[复制链接]
论坛徽章:
532
奥运会纪念徽章:垒球
日期: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-7-10 04:29 | 显示全部楼层 |阅读模式
最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正),其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。

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

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

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

作者: Kim Berg Hansen

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

我有一个集合类型,还有一张电影表:

create type qz_reviews as table of integer
/

create table qz_movies (
   id       integer primary key
, title    varchar2(30)
, reviews  qz_reviews
)
   nested table reviews store as qz_movie_reviews
/

insert into qz_movies values (1, 'Spiderman', null                  )
/
insert into qz_movies values (2, 'Titanic'  , qz_reviews()          )
/
insert into qz_movies values (3, 'Convoy'   , qz_reviews(5, 4, 5)   )
/
insert into qz_movies values (4, '2001'     , qz_reviews(5, 5, 5, 5))
/
insert into qz_movies values (5, 'Conan',     qz_reviews(1, 2, 5)   )
/
insert into qz_movies values (6, 'Batman',    qz_reviews(3, 3, 4)   )
/
commit
/

REVIEWS 这个嵌套表包含了这个电影得到的评分的集合,它的值是从1至5颗星。

我想要一个电影列表,列出至少得到过一次五星评分的电影。

哪些选项可以执行不出错,并且给了我这样一个输出列表:

        ID TITLE
---------- ------------------------------
         3 Convoy
         4 2001
         5 Conan

(A)
select m.id, m.title
  from qz_movies m
where m.reviews.exists(5)
order by m.id
/

(B)
select m.id, m.title
  from qz_movies m
where m.reviews.column_value = 5
order by m.id
/

(C)
select m.id, m.title
  from qz_movies m
     , table(m.reviews) r
where r.column_value = 5
order by m.id
/

(D)
select DISTINCT m.id, m.title
  from qz_movies m
     , table(m.reviews) r
where r.column_value = 5
order by m.id
/

(E)
select m.id, m.title
  from qz_movies m
where exists(
          select null
            from table(m.reviews) r
           where r.column_value = 5
       )
order by m.id
/

(F)
select m.id, m.title
  from qz_movies m
where 5 member of m.reviews
order by m.id
/

认证徽章
论坛徽章:
3
目光如炬
日期:2016-12-05 01:21:53秀才
日期:2016-12-21 16:55:07秀才
日期:2018-07-23 14:00:48
发表于 2018-7-10 09:11 | 显示全部楼层
D、E、F

A、B应该会报错,不存在这种用法。
C返回的结果会有重复的值。

使用道具 举报

回复
认证徽章
论坛徽章:
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-7-10 10:13 | 显示全部楼层
创建nesttable 一直提示:ORA-38818: illegal reference to editioned object APPS.QZ_REVIEWS ,没找到解决办法啊,newkid 大哥咋回事。请教一下。

使用道具 举报

回复
认证徽章
论坛徽章:
23
ITPUB十周年纪念徽章
日期:2011-11-01 16:26:29蒙奇·D·路飞
日期:2018-08-10 09:29:14弗兰奇
日期:2018-07-19 17:11:57娜美
日期:2018-06-22 09:24:50秀才
日期:2018-06-21 10:08:00ITPUB15周年纪念
日期:2018-03-20 15:45:33ITPUB15周年纪念
日期:2018-03-20 15:25:18托尼托尼·乔巴
日期:2018-02-05 08:12:49蒙奇·D·路飞
日期:2018-01-31 16:03:23秀才
日期:2018-01-02 15:35:05
发表于 2018-7-10 16:04 | 显示全部楼层
sse_zero 发表于 2018-7-10 10:13
创建nesttable 一直提示:ORA-38818: illegal reference to editioned object APPS.QZ_REVIEWS ,没找到解 ...

开启了 edition 吧

使用道具 举报

回复
论坛徽章:
532
奥运会纪念徽章:垒球
日期: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-7-11 04:27 | 显示全部楼层
答案DEF, 2楼得奖。

A: 集合函数EXISTS在SQL中是不可用的,仅在PL/SQL中可用。即使SQL有这个函数,它也是被用来查找数组中特定的索引位置,而不是查找特定值。但是既然它不能用在SQL中,这个选项会报错:
ORA-01747: invalid user.table.column, table.column, or column specification.

B: 当你在一个标量元素的嵌套表上使用TABLE函数时,COLUMN_VALUE是一个伪列(见下一选项)。但是它不可以在不带TABLE()的情况下直接使用,所以这会报错:
ORA-00904: "M"."REVIEWS"."COLUMN_VALUE": invalid identifier

C: 这是COLUMN_VALUE的正确用法,因为此处我们用了TABLE函数来为嵌套表集合中的每个值获取一行数据。但是既然我们在每个值上都得到一行,那么输出就会在每个五星评分的电影上都会包含一行。这意味着我们会得到这个错误输出:


        ID TITLE
---------- ------------------------------
         3 Convoy
         3 Convoy
         4 2001
         4 2001
         4 2001
         4 2001
         5 Conan

D:(不推荐)
我们可以通过在结果集上执行一个DISTINCT操作从而对前一选项进行一种“暴力法”的修正。这是可行的,但是做了不必要的工作。

E: 利用EXISTS子查询是另外一种方法,可以使得所预期的每个电影只给出一行,并且允许优化器在那个电影的嵌套表中找到一个五星的值就停止,因此比前一选项更加高效。

F:(推荐)
但是SQL也有一些MULTISET条件可以用,其中一个就是MEMBER OF。这是一个非常简短精练的办法来表达我们的目的,并且优化器会采取类似前一选项的措施来使得它可行。

使用道具 举报

回复
论坛徽章:
532
奥运会纪念徽章:垒球
日期: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-7-11 04:32 | 显示全部楼层
sse_zero 发表于 2018-7-10 10:13
创建nesttable 一直提示:ORA-38818: illegal reference to editioned object APPS.QZ_REVIEWS ,没找到解 ...

试试  ALTER USER 用户名 ENABLE EDITIONS;

使用道具 举报

回复
论坛徽章:
1
ITPUB社区12周年站庆徽章
日期:2013-09-18 15:36:43
发表于 2018-7-11 10:40 | 显示全部楼层
newkid 发表于 2018-7-11 04:32
试试  ALTER USER 用户名 ENABLE EDITIONS;

newkid您好,请教一下这个版本化有什么作用,enable 这个功能又什么后果,原来不是启用的。

使用道具 举报

回复
论坛徽章:
532
奥运会纪念徽章:垒球
日期: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-7-12 10:24 | 显示全部楼层
spjiang000 发表于 2018-7-11 10:40
newkid您好,请教一下这个版本化有什么作用,enable 这个功能又什么后果,原来不是启用的。

http://www.itpub.net/thread-1396183-1-1.html

使用道具 举报

回复

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

本版积分规则 发表回复

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