查看: 369|回复: 6

[每日一题] PL/SQL Challenge 每日一题:2018-9-27 层次查询

[复制链接]
论坛徽章:
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-10-9 23:48 | 显示全部楼层 |阅读模式
最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正),其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。

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

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

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

作者: Kim Berg Hansen

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

我有一个表,存放着考题(INSERT语句中的缩进存储是为了将层次结构可视化):

create table qz_topics (
   id          integer  primary key
, name        varchar2(30)
, parent_id   integer references qz_topics
)
/

insert into qz_topics values (10, 'SQL'         , null)
/
insert into qz_topics values (   20, 'Joins'       , 10)
/
insert into qz_topics values (      30, 'Inner'       , 20)
/
insert into qz_topics values (      40, 'Outer'       , 20)
/
insert into qz_topics values (   50, 'SET'         , 10)
/
insert into qz_topics values (60, 'PL/SQL'      , null)
/
insert into qz_topics values (   70, 'BEGIN'       , 60)
/
insert into qz_topics values (   80, 'Collections' , 60)
/
insert into qz_topics values (      90, 'VARRAY'      , 80)
/
commit
/

分支的最顶层(没有父节点的根)是类别。
分支的最底层(没有子节点的叶)是题目。
中间的层次(既有父节点又有子节点)是题目类别之内的子类别。

我想要一个所有题目的列表,显示其类别以及题目名称。在这个列表中我对子类别不感兴趣。

哪些选项包含的查询可以执行不出错,产生如下的列表:

CATEGORY_NAME                  TOPIC_NAME
------------------------------ ------------------------------
PL/SQL                         BEGIN
PL/SQL                         VARRAY
SQL                            Inner
SQL                            Outer
SQL                            SET

(A)
select connect_by_root name as category_name
     , name as topic_name
  from qz_topics
where connect_by_isleaf = 1
start with parent_id is null
connect by parent_id = prior id
order by category_name, topic_name
/

(B)
select (
         select max(parents.name)
                      keep (dense_rank last order by level)
           from qz_topics parents
          start with parents.id = qz_topics.parent_id
          connect by parents.id = prior parents.parent_id
       ) as category_name
     , name as topic_name
  from qz_topics
where not exists (
         select null
           from qz_topics children
          where children.parent_id = qz_topics.id
       )
order by category_name, topic_name
/

(C)
select (
         select parents.name
           from qz_topics parents
          where connect_by_isleaf = 1
          start with parents.id = qz_topics.parent_id
          connect by parents.id = prior parents.parent_id
       ) as category_name
     , name as topic_name
  from qz_topics
where not exists (
         select null
           from qz_topics children
          where children.parent_id = qz_topics.id
       )
order by category_name, topic_name
/

(D)
select name as category_name
     , connect_by_root name as topic_name
  from qz_topics
where connect_by_isleaf = 1
start with id not in (
          select parent_id
            from qz_topics
           where parent_id is not null
       )
connect by id = prior parent_id
order by category_name, topic_name
/

论坛徽章:
401
紫蛋头
日期: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-10-10 13:35 | 显示全部楼层
select null 为什么不写成  select 1

使用道具 举报

回复
论坛徽章:
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-10-11 04:45 | 显示全部楼层
〇〇 发表于 2018-10-10 13:35
select null 为什么不写成  select 1

TOM说的。因为不需要任何数据,只是检查行是否存在,NULL清楚地表明了这一点。

使用道具 举报

回复
论坛徽章:
2
娜美
日期:2018-09-21 08:59:27托尼托尼·乔巴
日期:2018-09-28 17:29:58
发表于 2018-10-11 08:55 | 显示全部楼层
学习了

使用道具 举报

回复
论坛徽章:
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-10-11 15:01 | 显示全部楼层
答案: ABCD

A: connect_by_root 可以返回根节点,where中的 connect_by_isleaf = 1 可以过滤取得叶子节点,简洁,推荐
B,C,D 也都可以,但都比较啰嗦

使用道具 举报

回复
认证徽章
论坛徽章:
34
林肯
日期:2013-07-30 18:00:55技术图书徽章
日期:2018-05-22 16:13:30秀才
日期:2018-05-22 16:13:30秀才
日期:2018-05-22 16:17:26秀才
日期:2018-07-23 13:38:29技术图书徽章
日期:2018-07-23 13:38:40秀才
日期:2018-07-23 13:38:40秀才
日期:2018-07-23 13:44:01秀才
日期:2018-08-31 10:37:01秀才
日期:2018-08-31 10:37:20
发表于 2018-10-11 15:04 | 显示全部楼层
学习了。

使用道具 举报

回复
论坛徽章:
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-10-12 04:01 | 显示全部楼层
答案ABCD, 5楼得奖。

A:(推荐)
此处我们从树的最顶层节点(那些没有父亲的节点)开始,自顶向下遍历这棵树。所以当我们过滤并且仅仅保留树中的叶节点(没有孩子的节点),我们就得到了所需的数据行。那一行中的name就是题目名称,而那个分支的根节点的name则是类别名称。

B: 此处我们从没有孩子的节点开始,这些就是我们所感兴趣的行(层次结构中的叶子)。那些行的name就是题目名称。对应每一行我们执行了一个标量子查询,它自底向上遍历了树,从叶节点的父亲开始,直到顶端为止。于是顶端的节点的层次是最高的,所以利用KEEP子句我们就只取到最顶节点的名称,也就是类别名称。

C: 几乎和前一选项相同,只是没有用KEEP来获得类别名称,而是在标量子查询中用了CONNECT_BY_ISLEAF来过滤,因为我们是从层次的底端开始并且往上移动,顶端节点实际上变成这个倒挂的树的叶节点,于是就得到了所要的类别名称。

D: A选项是从顶端节点开始并且向下遍历树,而这个选项则是从没有孩子的节点开始并且向上遍历树。因此就如同前一选项中的标量子查询,这个倒挂的树的叶节点就是类别节点。而倒挂的树的根则是题目节点。

使用道具 举报

回复

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

本版积分规则 发表回复

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