查看: 3841|回复: 6

[每日一题] PL/SQL Challenge 每日一题:2018-3-24 CONNECT BY层次查询

[复制链接]
论坛徽章:
526
奥运会纪念徽章:垒球
日期: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-3-29 03:49 | 显示全部楼层 |阅读模式

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

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

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

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

作者: Kim Berg Hansen

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

我有一张员工表,它带有一个指向自身的外键,指明谁是谁的经理:

create table qz_emp (
   empno    number primary key
, ename    varchar2(10)
, mgr      number references qz_emp
)
/

insert into qz_emp values (7839, 'KING'  , null)
/
insert into qz_emp values (7566, 'JONES' , 7839)
/
insert into qz_emp values (7788, 'SCOTT' , 7566)
/
insert into qz_emp values (7876, 'ADAMS' , 7788)
/
insert into qz_emp values (7902, 'FORD'  , 7566)
/
insert into qz_emp values (7369, 'SMITH' , 7902)
/
insert into qz_emp values (7698, 'BLAKE' , 7839)
/
insert into qz_emp values (7499, 'ALLEN' , 7698)
/
insert into qz_emp values (7521, 'WARD'  , 7698)
/
insert into qz_emp values (7654, 'MARTIN', 7698)
/
insert into qz_emp values (7844, 'TURNER', 7698)
/
insert into qz_emp values (7900, 'JAMES' , 7698)
/
insert into qz_emp values (7782, 'CLARK' , 7839)
/
insert into qz_emp values (7934, 'MILLER', 7782)
/
commit
/

我想要一个以树形层次显示的员工列表,从没有经理的员工开始,然后跟随着树中往下的层次。树中的兄弟节点(同一个经理下的同级别员工)必须以列名ENAME排序。

我有这个未完成的查询来产生这个列表:

select empno
     , lpad(' ', (level - 1) * 2) || ename as ename
     , mgr
  from qz_emp
##REPLACE##
/

哪些选项包含的代码可用来取代 ##REPLACE##,使得查询执行不出错并产生这个所需的输出:

     EMPNO ENAME                       MGR
---------- -------------------- ----------
      7839 KING
      7698   BLAKE                    7839
      7499     ALLEN                  7698
      7900     JAMES                  7698
      7654     MARTIN                 7698
      7844     TURNER                 7698
      7521     WARD                   7698
      7782   CLARK                    7839
      7934     MILLER                 7782
      7566   JONES                    7839
      7902     FORD                   7566
      7369       SMITH                7902
      7788     SCOTT                  7566
      7876       ADAMS                7788

(A)
start with mgr is null
connect by mgr = prior empno
order by level, ename

(B)
connect by mgr = prior empno
start with mgr is null
order by level, ename

(C)
start with mgr is null
connect by mgr = prior empno
order levels by ename

(D)
connect by mgr = prior empno
start with mgr is null
order levels by ename

(E)
start with mgr is null
connect by mgr = prior empno
order siblings by ename


(F)
connect by mgr = prior empno
start with mgr is null
order siblings by ename

(G)
start with mgr is null
connect by mgr = prior empno
order siblings by qz_emp.ename

(H)
connect by mgr = prior empno
start with mgr is null
order siblings by qz_emp.ename
论坛徽章:
2
秀才
日期:2017-09-25 15:10:56秀才
日期:2018-01-02 15:05:22
发表于 2018-3-29 09:13 | 显示全部楼层
感谢kid 版主的无私奉献,peace and love!

使用道具 举报

回复
认证徽章
论坛徽章:
0
发表于 2018-3-29 11:28 | 显示全部楼层
ABGH
A和B 没啥问题,最常见的,start with 和 connect by 顺序应该没啥区别。
CD order levels by ename 没见过这个用法,试了一下确实不行。
EF order siblings by ename 也没见过,跳出来ora-00976:不允许指定的伪劣或运算符。
     但是列ename 指定了表名 就可以了,还请newkid大神赐教

使用道具 举报

回复
论坛徽章:
326
生肖徽章2007版:猴
日期:2008-05-16 11:28:59生肖徽章2007版:马
日期:2008-10-08 17:01:01SQL大赛参与纪念
日期:2011-04-13 12:08:17授权会员
日期:2011-06-17 16:14:53ITPUB元老
日期:2011-06-21 11:47:01ITPUB官方微博粉丝徽章
日期:2011-07-01 09:45:27ITPUB十周年纪念徽章
日期:2011-09-27 16:30:472012新春纪念徽章
日期:2012-01-04 11:51:22海蓝宝石
日期:2012-02-20 19:24:27铁扇公主
日期:2012-02-21 15:03:13
发表于 2018-3-29 11:52 | 显示全部楼层
答案:GH

A: start with,connect by 写法是对的,但order by level, ename 排序结果是按等级和别名,不满足要求
B: 与A 同, start with 和 connect by ,好像谁在前谁在后的写法是等价的
C: order levels BY ,没有这样的语法
D: 同C
E: order siblings by 树中的兄弟节点排序是样的写法,但这里 ename 已经是别名,会报:
   ORA-00976: Specified pseudocolumn or operator not allowed here.
F: 同E
G: 正确写法,排序的字段用表名或表别名以区分字段别名
H: 同G
   

使用道具 举报

回复
论坛徽章:
1
秀才
日期:2018-05-22 15:26:25
发表于 2018-3-29 16:03 | 显示全部楼层
答案: G, H
A:  结果整体排序, 体现不了上下层次关系.
B:  START WITH CONNECT BY 位置可以互换,  但问题与A相同.
C:  没有order levels by 的用法.
D:  同C
E:  SELECT 别名 ename ,  与表列名 ename重复,  作为CLAUSE的别名ename中, 没有层次结构的含义, 不能用在order siblings by.
F:  同E
G:  可通过.  定义同层次结构的排序方式.  
H:  同G  . start with connect by 可互换

使用道具 举报

回复
论坛徽章:
1
弗兰奇
日期:2018-03-29 11:10:12
发表于 2018-3-29 17:11 | 显示全部楼层
第一次接触,学习了!但是还是没有弄明白里面的关系

使用道具 举报

回复
论坛徽章:
526
奥运会纪念徽章:垒球
日期: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-3-30 08:48 | 显示全部楼层
答案GH, 4楼得奖。

A: 按照LEVEL, ENAME排序不会给出我们所需的顺序。相反,我们会得到这个错误输出:先是LEVEL 1的所有人(只有KING一人),然后是LEVEL 2的所有人(以名字排序),然后是LEVEL 3,等等:

     EMPNO ENAME                       MGR
---------- -------------------- ----------
      7839 KING
      7698   BLAKE                    7839
      7782   CLARK                    7839
      7566   JONES                    7839
      7499     ALLEN                  7698
      7902     FORD                   7566
      7900     JAMES                  7698
      7654     MARTIN                 7698
      7934     MILLER                 7782
      7788     SCOTT                  7566
      7844     TURNER                 7698
      7521     WARD                   7698
      7876       ADAMS                7788
      7369       SMITH                7902

B: 将START WITH和CONNECT BY调换顺序是允许的,我们得到的是和前一选项相同的错误结果。
C: ORDER LEVELS 是假语法,它并不存在。这个选项会报错:
ORA-00924: missing BY keyword.
D: 这个选项和前一选项有同样的问题。
E: ORDER SIBLINGS是得到我们所需顺序的正确办法,但是我们指定的ENAME被解释为列别名ENAME。ORDER SIBLINGS不能够用列别名中的表达式来排序,所以这个选项会报错:
  ORA-00976: Specified pseudocolumn or operator not allowed here.
F: 这个选项和前一选项得到的是同样的错误。
G: 通过将ORDER SIBLINGS子句中的ENAME加以修饰,我们迫使它解释为表的列名ENAME而不是列别名为ENAME的表达式。这样的话我们就得到了所需的结果。
H: 正如B选项所解释的,将START WITH和CONNECT BY调换顺序不会有什么区别,所以这和前一选项是等价的。

使用道具 举报

回复

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

本版积分规则 发表回复

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