ITPUB论坛-中国最专业的IT技术社区

 找回密码
 注册
查看: 4497|回复: 4

[每日一题] PL/SQL Challenge 每日一题:2017-6-8 SQL比较操作符: ALL, ANY 和 SOME

[复制链接]
论坛徽章:
480
榜眼
日期:2015-09-09 10:34:21秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09
发表于 2017-6-14 05:16 | 显示全部楼层 |阅读模式

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

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

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

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

作者:Kim Berg Hansen

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

在我们的角色扮演网站上,我有一个玩家角色表:

create table qz_players (
   id       integer primary key
, name     varchar2(10)
, gender   varchar2(1) not null check (gender in ('M', 'F'))
, stage    varchar2(1) not null check (stage in ('B', 'I', 'A'))
)
/

insert into qz_players values (42, 'Stardancer', 'F', 'B')
/
insert into qz_players values (43, 'Moonlight' , 'F', 'I')
/
insert into qz_players values (44, 'Sunshine'  , 'F', 'A')
/
insert into qz_players values (45, 'Tychonian' , 'M', 'B')
/
insert into qz_players values (46, 'Marsdust'  , 'M', 'I')
/
insert into qz_players values (47, 'Mercury'   , 'M', 'A')
/
commit
/

玩家角色可以是男性或女性,并且处于三个不同的发展阶段(stage)之一:初级B,中级I 或高级A。

我想要一张玩家清单,这些玩家既不是高级阶段的女性,也不是初级阶段的男性。

为此我写了这个未完成的查询:

select id, name, gender, stage
  from qz_players
##REPLACE##
order by id
/

哪些选项包含了一个WHERE子句,可以用来取代 ##REPLACE##,使得查询返回这个输出:

        ID NAME       G S
---------- ---------- - -
        42 Stardancer F B
        43 Moonlight  F I
        46 Marsdust   M I
        47 Mercury    M A
        
(A)
where NOT (gender = 'F' and stage = 'A')
   and NOT (gender = 'M' and stage = 'B')

(B)
where (gender, stage) != ('F', 'A')
   and (gender, stage) != ('M', 'B')

(C)
where (gender, stage) != ( ('F', 'A') )
   and (gender, stage) != ( ('M', 'B') )

(D)
where (gender, stage) != ( ('F', 'A'), ('M', 'B') )

(E)
where (gender, stage) != ALL ( ('F', 'A'), ('M', 'B') )

(F)
where NOT (gender, stage) = ALL ( ('F', 'A'), ('M', 'B') )

(G)
where NOT (gender, stage) = ANY ( ('F', 'A'), ('M', 'B') )

(H)
where (gender, stage) = ANY ( ('F', 'B'), ('F', 'I')
                             , ('M', 'I'), ('M', 'A') )

(I)
where NOT (gender, stage) != ANY ( ('F', 'B'), ('F', 'I')
                                  , ('M', 'I'), ('M', 'A') )

(J)
where NOT (gender, stage) != ALL ( ('F', 'B'), ('F', 'I')
                                  , ('M', 'I'), ('M', 'A') )

(K)
where (gender, stage) NOT IN ( ('F', 'A'), ('M', 'B') )

(L)
where (gender, stage) NOT IN ALL ( ('F', 'A'), ('M', 'B') )
论坛徽章:
394
阿斯顿马丁
日期:2014-01-03 13:53:522014年世界杯参赛球队:喀麦隆
日期:2014-07-11 12:10:53马上有对象
日期:2014-04-09 16:19:542014年世界杯参赛球队: 洪都拉斯
日期:2014-06-25 08:25:55itpub13周年纪念徽章
日期:2014-09-28 10:55:55itpub13周年纪念徽章
日期:2014-10-01 15:27:22itpub13周年纪念徽章
日期:2014-10-09 12:04:18马上有钱
日期:2014-10-14 21:37:37马上有钱
日期:2015-01-22 00:39:13喜羊羊
日期:2015-02-20 22:26:07
发表于 2017-6-14 10:40 | 显示全部楼层
本帖最后由 〇〇 于 2017-6-14 10:42 编辑

= != 只能用于单个比较
SQL> select * from dual where ('A',1)=('A',1);
select * from dual where ('A',1)=('A',1)
                                 *
第 1 行出现错误:
ORA-00920: 无效的关系运算符


SQL> select * from dual where ('A',1)in(('A',1));

DU
--
X

使用道具 举报

回复
论坛徽章:
394
阿斯顿马丁
日期:2014-01-03 13:53:522014年世界杯参赛球队:喀麦隆
日期:2014-07-11 12:10:53马上有对象
日期:2014-04-09 16:19:542014年世界杯参赛球队: 洪都拉斯
日期:2014-06-25 08:25:55itpub13周年纪念徽章
日期:2014-09-28 10:55:55itpub13周年纪念徽章
日期:2014-10-01 15:27:22itpub13周年纪念徽章
日期:2014-10-09 12:04:18马上有钱
日期:2014-10-14 21:37:37马上有钱
日期:2015-01-22 00:39:13喜羊羊
日期:2015-02-20 22:26:07
发表于 2017-6-14 10:44 | 显示全部楼层
〇〇 发表于 2017-6-14 10:40
= != 只能用于单个比较
SQL> select * from dual where ('A',1)=('A',1);
select * from dual where ('A' ...

如果=和any all等配合就可以
SQL> select * from dual where ('A',1)=any(('A',1));

DU
--
X

SQL> select * from dual where ('A',1)=some(('A',1));

DU
--
X

SQL> select * from dual where ('A',1)=all(('A',1));

DU
--
X

使用道具 举报

回复
认证徽章
论坛徽章:
6
秀才
日期:2017-06-29 10:16:48技术图书徽章
日期:2017-06-29 10:17:04秀才
日期:2017-06-29 10:17:04秀才
日期:2017-08-11 15:38:34秀才
日期:2017-08-11 15:38:34秀才
日期:2017-08-23 14:12:15
发表于 2017-6-14 16:58 | 显示全部楼层
答案是A、C、E、G、H、J、K。
A.将not后的表达式的值当做布尔型true or false.原表达式为假时,为真,输出结果可以满足楼主要求;
B.根据syntax,组比较时在表达式外侧应用一对括号包围;
C.正确,B.选项的修正版;
D.不应使用不等于,这里只能作单组比较不能将左边的字段组和右边的两组值同时作“=”或“!=”比较,可以改为 not in;
E.由于右侧加了ALL关键字,故正确;
F.和D.类似,ALL代表和列表/集合中的每一个/所有元素作比较,ANY代表和其中的任意一个作比较。不能拿左边的一个元素去和右边的所有元素同时比较,因此此处应用ANY;
G.正确,F.选项的正确形式;
H.正确,之前的选项思维都是逆向思维,取不满足条件的数据的补集,这个选项则是正向思维,取满足条件的数据;
I.NOT != ANY这样用是错的,NOT得到的是个集合,ANY是任意一个元素,量级不同,无法比较;
J.正确,将J.中的ANY替换为ALL,量级相同,可以比较;
K.这就是我在D.选项解释里所说的用not in 替换的正确形式;
L.ALL属于多余,ALL只能跟在=,!=,>,<,^=,<>,>=,<=操作符后面,否则会报错,详见http://docs.oracle.com/database/ ... ions.htm#SQLRF52110

使用道具 举报

回复
论坛徽章:
480
榜眼
日期:2015-09-09 10:34:21秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09
 楼主| 发表于 2017-6-15 04:31 | 显示全部楼层
答案ACEGHJK, 4楼得奖。

A: 简单且容易的布尔条件——玩家不能同时是女性及高级阶段,并且不能同时为男性及初级阶段。
B: 我们也可以比较表达式列表,而不是单个列比较。但是这里的语法是错误的,我们缺少一套括号,所以会报错:
ORA-00920: invalid relational operator.
C: 字面常量外面的两层括号是正确的。外层的括号标记着一个元素列表的开始及结尾——此例中是一个元素的列表,这个元素包含两个表达式。把最内层括号想像为“记录”,最外层想像成某种“数组”,这样可以帮助理解。
D: 此处的表达式列表为两个元素的列表,每个元素包含两个表达式——这是一个正确的表达式列表。但是你不能够在一个多元素表达式列表(或者多于一个元素的“数组”)上面直接使用不等比较符,这会报错:
ORA-00920: invalid relational operator.
E: (推荐)
但是,在表达式列表之前加上ALL关键字,这表示着不等比较必须在列表的所有元素上有效。实际上这和C是等价的,会给我们相同的输出。
F: 此处我们没有用不等比较符,而是用了等值比较,然后用NOT对整个子句取反。这对于简单的不等比较是有效的,但却不能用于此处。在这个选项中,我们用等值比较符和ALL测试两个列是否等于(F,A),同时等于(M,B),这当然不可能,所以等值比较符永远返回FALSE。然后我们用了NOT将FALSE变成TRUE, 于是这个选项返回了所有6行数据。

G: 将上一选项的ALL替换成ANY就行了,因为现在等值比较符和ANY测试的是两个列是否等于(F,A),或者等于(M,B),这对于我们要过滤掉的两行是TRUE,然后NOT给了我们所需的结果。

H: 我们也可以将逻辑反过来——不是将两个不要的组合过滤掉,而是把四个想要的组合保留下来。此处的ANY等价于执行对清单中的四个元素分别执行OR等值比较。
I: 再做一个“双重否定”是很棘手的。 在这种情况下,当我们使用ANY测试不等式时,我们实际上说它不能等于第一个元素,或者不等于第二个元素,或者...等等。这永远是对的,所以不等式返回始终为TRUE,我们取反为FALSE,因此这个选项不会返回任何行。
J: 将ANY改成ALL就可以挽救这个“双重否定”,实际上意味着我们说的是它不能够等于第一个元素,并且不能等于第二个元素,并且...等等。这仅对我们要过滤的两行是TRUE,所以用NOT给了我们所需的输出。
K: NOT IN 同样支持表达式列表,这和E等价。
L: 但是 NOT IN 和 ALL不能一起用,这会报错:
ORA-00920: invalid relational operator.

使用道具 举报

回复

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

本版积分规则

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