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

 找回密码
 注册
查看: 1314|回复: 7

[每日一题] PL/SQL Challenge 每日一题:2018-4-3 DECODE函数

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

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

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

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

作者: Kim Berg Hansen

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

我有一张书籍表:

create table qz_books (
   id       integer primary key
, title    varchar2(30)
, favorite varchar2(1) check (favorite in ('Y', 'N')))
/

insert into qz_books values (1, 'The Number of the Beast', 'Y');
insert into qz_books values (2, 'War and Peace'          , 'N');
insert into qz_books values (3, 'Origin'                 , null);
commit;

在FAVORITE列我可以用Y和N来表示这本书是不是我所喜爱的。如果这个列没有输入任何指示(NULL),这是因为我还没有读过这本书。

我想要一个书籍列表,带有一个列MY_FAVORITE,用文本来表示FAVORITE列的内容,于是'Y'被写成'Yes', 'N' 被写成'No', 如果没有指示则写成"(Not read yet)".

为了产生这个列表我写了这个未完成的查询:

select id
     , title
##REPLACE##
  from qz_books
order by id
/

哪些选项包含了一个列别名为MY_FAVORITE的表达式,可以用来取代##REPLACE## 使得查询返回这个输出:

        ID TITLE                          MY_FAVORITE
---------- ------------------------------ ---------------
         1 The Number of the Beast        Yes
         2 War and Peace                  No
         3 Origin                         (Not read yet)

(A)
, decode(favorite
        , '' , '(Not read yet)'
        , 'Y', 'Yes'
        , 'N', 'No'
             , 'Not possible'
       ) as my_favorite

(B)
, decode(favorite
        , NULL , '(Not read yet)'
        , 'Y'  , 'Yes'
        , 'N'  , 'No'
               , 'Not possible'
       ) as my_favorite

(C)
     , decode(favorite
        , 'Y', 'Yes'
        , 'N', 'No'
             , '(Not read yet)'
       ) as my_favorite

(D)
     , case favorite
          when ''  then '(Not read yet)'
          when 'Y' then 'Yes'
          when 'N' then 'No'
                   else 'Not possible'
       end as my_favorite

(E)
     , case favorite
          when NULL then '(Not read yet)'
          when 'Y'  then 'Yes'
          when 'N'  then 'No'
                    else 'Not possible'
       end as my_favorite
      
(F)
     , case favorite
          when 'Y'  then 'Yes'
          when 'N'  then 'No'
                    else '(Not read yet)'
       end as my_favorite      

(G)
     , case
          when favorite is NULL then '(Not read yet)'
          when favorite = 'Y'   then 'Yes'
          when favorite = 'N'   then 'No'
                                else 'Not possible'
       end as my_favorite
论坛徽章:
23
秀才
日期:2015-10-26 09:55:082016猴年福章
日期:2016-02-23 09:58:342013年新春福章
日期:2013-02-25 14:51:242016猴年福章
日期:2016-02-18 09:31:30秀才
日期:2015-12-21 09:53:46猴赛雷
日期:2016-02-18 09:33:42布鲁克
日期:2018-06-12 08:18:552015年新春福章
日期:2015-03-06 11:59:47布鲁克
日期:2018-05-23 21:58:37猴赛雷
日期:2016-02-23 10:04:24
发表于 2018-4-13 08:27 | 显示全部楼层
因為decode函数是将基準值與各search值一一比對。假使没有匹配的search值則返回基準值;若函数中基準值缺省則返回null。
故decode是可以判斷null的。反之case 則必須使用is null 或is not null 來判斷(或者搭配nvl)。
故答案為A、B、C、F、G

使用道具 举报

回复
认证徽章
论坛徽章:
21
林肯
日期:2013-07-30 18:00:55秀才
日期:2018-05-22 15:17:21秀才
日期:2018-05-22 15:21:20技术图书徽章
日期:2018-05-22 15:21:47秀才
日期:2018-05-22 15:21:47秀才
日期:2018-05-22 16:13:08秀才
日期:2018-05-22 16:13:21技术图书徽章
日期:2018-05-22 16:13:30秀才
日期:2018-05-22 16:13:30技术图书徽章
日期:2018-05-22 15:17:21
发表于 2018-4-13 09:15 | 显示全部楼层
A 竟然时候对的,NULL和''怎么会一样呢。不理解。

使用道具 举报

回复
认证徽章
论坛徽章:
21
ITPUB十周年纪念徽章
日期:2011-11-01 16:26:29娜美
日期: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-01-02 15:30:09技术图书徽章
日期:2017-12-21 14:05:31
发表于 2018-4-13 10:43 | 显示全部楼层
sse_zero 发表于 2018-4-13 09:15
A 竟然时候对的,NULL和''怎么会一样呢。不理解。

影響中 ''不在where col='' 謂詞部分話和NULL是一回事

使用道具 举报

回复
论坛徽章:
2
秀才
日期:2018-05-22 16:17:26秀才
日期:2018-05-22 16:17:40
发表于 2018-4-13 10:53 | 显示全部楼层
答案是ABCFG
理由是case不可直接用null或空字符串

使用道具 举报

回复
论坛徽章:
400
紫蛋头
日期:2012-05-21 10:19:41迷宫蛋
日期:2012-06-06 16:02:49奥运会纪念徽章:足球
日期:2012-06-29 15:30:06奥运会纪念徽章:排球
日期:2012-07-10 21:24:24鲜花蛋
日期:2012-07-16 15:24:59奥运会纪念徽章:拳击
日期:2012-08-07 10:54:50奥运会纪念徽章:羽毛球
日期:2012-08-21 15:55:33奥运会纪念徽章:蹦床
日期:2012-08-21 21:09:51奥运会纪念徽章:篮球
日期:2012-08-24 10:29:11奥运会纪念徽章:体操
日期:2012-09-07 16:40:00
发表于 2018-4-13 13:27 | 显示全部楼层
sse_zero 发表于 2018-4-13 09:15
A 竟然时候对的,NULL和''怎么会一样呢。不理解。

SQL> select * from dual where '' is null;

D
-
X

SQL> select * from dual where ''='';

no rows selected

使用道具 举报

回复
论坛徽章:
400
紫蛋头
日期:2012-05-21 10:19:41迷宫蛋
日期:2012-06-06 16:02:49奥运会纪念徽章:足球
日期:2012-06-29 15:30:06奥运会纪念徽章:排球
日期:2012-07-10 21:24:24鲜花蛋
日期:2012-07-16 15:24:59奥运会纪念徽章:拳击
日期:2012-08-07 10:54:50奥运会纪念徽章:羽毛球
日期:2012-08-21 15:55:33奥运会纪念徽章:蹦床
日期:2012-08-21 21:09:51奥运会纪念徽章:篮球
日期:2012-08-24 10:29:11奥运会纪念徽章:体操
日期:2012-09-07 16:40:00
发表于 2018-4-13 13:29 | 显示全部楼层
〇〇 发表于 2018-4-13 13:27
SQL> select * from dual where '' is null;

D

还有


SQL> select * from dual where '' =trim('   ');

no rows selected

SQL> select * from dual where trim('   ') is null;                        

D
-
X

使用道具 举报

回复
论坛徽章:
496
紫蜘蛛
日期:2007-09-26 17:05:56奥运会纪念徽章:垒球
日期: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:53
 楼主| 发表于 2018-4-14 01:50 | 显示全部楼层
答案ABCFG,2楼得奖。

A: 在Oracle中,空字符串(两个单引号)和NULL是相同的。通常将NULL和任何东西比较会得到UNKNOWN(未知)结果,但是DECODE函数是SQL中少数的将NULL和NULL比较会得到TRUE的情形之一。所以对于id=3这本书,列中的NULL被和DECODE函数中的''比较,给了我们所需要的结果。

B: 在ORACLE SQL中指定NULL而不是像前一选项那样使用空串,得到的结果完全相同。但是这会更加清楚,特别是对于那些习惯了其他数据库的程序员来说,在那些库中空串和NULL可能是有区别的。
C: 既然从检查约束中我们已经得知,FAVORITE列的有效内容只能是Y,N和NULL,我们也可以决定不对NULL进行显式测试,而是让NULL落在DECODE的最后一个参数,这就是当FAVORITE列既不是'Y'也不是'N'时返回的值。
D: 利用简单CASE表达式在表面上看起来和DECODE很像,只是文本更加明确,而不是只有一堆逗号。但是区别在于每个WHEN子句被处理成"FAVORITE = 值"的条件,所以此处列表中的第一个就被处理成FAVORITE = '', 也就是和FAVORITE = NULL相同,结果是UNKNOWN(布尔型的NULL),而不是TRUE。因此id=3落入了ELSE子句,我们得到这个错误输出:

        ID TITLE                          MY_FAVORITE
---------- ------------------------------ ---------------
         1 The Number of the Beast        Yes
         2 War and Peace                  No
         3 Origin                         Not possible

E: 明确地使用NULL而不是''显然不会有任何区别,我们得到了和前一选项相同的错误结果。
F: 但是对于CASE语句我们也可以(和选项C相同)利用已知的这一事实,即FAVORITE的值既不是Y也不是N时,就必须为NULL。所以我们可以用ELSE子句来捕获所有的NULL并且得到所需的输出。
G: 用CASE处理NULL的另一个方法是从简单型CASE语句变成搜索型CASE语句,这是你可以用你所需的条件并且使用IS NULL条件而不是等值比较。

使用道具 举报

回复

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

本版积分规则

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