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

 手机号登录  找回密码
 注册
查看: 566|回复: 7

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

[复制链接]
论坛徽章:
486
秀才
日期:2015-09-09 10:33:01秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21
发表于 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
论坛徽章:
16
秀才
日期:2015-10-26 09:55:08美羊羊
日期:2015-03-04 14:54:272013年新春福章
日期:2013-02-25 14:51:24知识
日期:2015-07-06 11:21:47秀才
日期:2015-12-21 09:53:46猴赛雷
日期:2016-02-18 09:33:422016猴年福章
日期:2016-02-23 09:58:342015年新春福章
日期:2015-03-06 11:59:472016猴年福章
日期:2016-02-18 09:31:30猴赛雷
日期: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

使用道具 举报

回复
认证徽章
论坛徽章:
10
林肯
日期:2013-07-30 18:00:55技术图书徽章
日期:2018-03-01 10:21:49秀才
日期:2018-03-01 10:21:25秀才
日期:2018-03-01 10:05:18秀才
日期:2015-12-14 15:09:382015年新春福章
日期:2015-03-06 11:58:18懒羊羊
日期:2015-03-04 14:52:11马上有对象
日期:2015-01-16 13:26:19优秀写手
日期:2014-10-23 06:00:14秀才
日期:2018-03-01 10:21:49
发表于 2018-4-13 09:15 | 显示全部楼层
A 竟然时候对的,NULL和''怎么会一样呢。不理解。

使用道具 举报

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

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

使用道具 举报

回复
论坛徽章:
0
发表于 2018-4-13 10:53 | 显示全部楼层
答案是ABCFG
理由是case不可直接用null或空字符串

使用道具 举报

回复
论坛徽章:
398
优秀写手
日期:2013-12-18 09:29:08itpub13周年纪念徽章
日期: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懒羊羊
日期:2015-02-21 22:03:31懒羊羊
日期:2015-03-04 14:52:112015年新春福章
日期:2015-03-06 11:58:18
发表于 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

使用道具 举报

回复
论坛徽章:
398
优秀写手
日期:2013-12-18 09:29:08itpub13周年纪念徽章
日期: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懒羊羊
日期:2015-02-21 22:03:31懒羊羊
日期:2015-03-04 14:52:112015年新春福章
日期:2015-03-06 11:58:18
发表于 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

使用道具 举报

回复
论坛徽章:
486
秀才
日期:2015-09-09 10:33:01秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21
 楼主| 发表于 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号
  
快速回复 返回顶部 返回列表