查看: 750|回复: 7

[每日一题] PL/SQL Challenge 每日一题:2018-6-7 SYS_CONNECT_BY_PATH

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

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

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

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

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

作者: Kim Berg Hansen

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

我有一张表包含着城市,州名和国家名称,以层次的关系展现:

create table qz_geography (
   id          integer  primary key
, name        varchar2(30)
, type        varchar2(10)
, parent_id   integer references qz_geography
)
/

insert into qz_geography values (1, 'Germany'      , 'Country', null)
/
insert into qz_geography values (2, 'Brandenburg'  , 'State'  , 1   )
/
insert into qz_geography values (3, 'Zossen'       , 'City'   , 2   )
/
insert into qz_geography values (4, 'Niedersachsen', 'State'  , 1   )
/
insert into qz_geography values (5, 'Fleestedt'    , 'City'   , 4   )
/
insert into qz_geography values (6, 'USA'          , 'Country', null)
/
insert into qz_geography values (7, 'New York'     , 'State'  , 6   )
/
insert into qz_geography values (8, 'Wayland'      , 'City'   , 7   )
/
insert into qz_geography values (9, 'California'   , 'State'  , 6   )
/
insert into qz_geography values (10, 'Los Angeles' , 'City'   , 9   )
/
insert into qz_geography values (11, 'Long Beach'  , 'City'   , 9   )
/
commit
/

国家没有父节点,州的父节点是国家,城市的父节点是州。

我想要一个城市清单,同时表明这个城市处于哪个州,哪个国家。它必须以一个CITY列展示,文本格式为“城市,州,国家”。

哪些选项包含的查询会产生这个清单:

CITY
------------------------------------------------------------
Fleestedt, Niedersachsen, Germany
Long Beach, California, USA
Los Angeles, California, USA
Wayland, New York, USA
Zossen, Brandenburg, Germany

(A)
select substr(sys_connect_by_path(name, ', '), 3) city
  from qz_geography
where connect_by_isleaf = 1
start with parent_id is null
connect by parent_id = prior id
order by city
/

(B)
select substr(sys_connect_by_path(
                 name, ', '
                 order by level desc
              ), 3) city
  from qz_geography
where connect_by_isleaf = 1
start with type = 'Country'
connect by parent_id = prior id
order by city
/


(C)
select substr(sys_connect_by_path(name, ', '), 3) city
  from qz_geography
where level = 3
start with type = 'City'
connect by id = prior parent_id
order by city
/



(D)

select substr(sys_connect_by_path(name, ', '), 3) city
  from qz_geography
where connect_by_isleaf = 1
start with type = 'City'
connect by id = prior parent_id
order by city
/


(E)
select city.name || ', ' || state.name || ', ' || country.name city
  from qz_geography city
  join qz_geography state
      on state.id = city.parent_id
  join qz_geography country
      on country.id = state.parent_id
where city.type = 'City'
order by city
/


(F)
select city.name || ', ' || state.name || ', ' || country.name city
  from qz_geography city
  join qz_geography state
      on state.type = 'State'
  join qz_geography country
      on country.type = 'Country'
where city.type = 'City'
order by city
/

论坛徽章:
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-6-12 08:17 | 显示全部楼层
e不错

使用道具 举报

回复
论坛徽章:
368
生肖徽章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-6-12 09:50 | 显示全部楼层
答案:CDE
A: connect by prior id = parent_id 这是由父节点到子节点连接,顺序反了
B: sys_connect_by_path 没有 order by 子句
C: connect by id = prior parent_id 由子节点向父节点连接,且起始节点为第三层的CITY节点
D: connect_by_isleaf = 1 取包含叶子节点的,与C的 level = 3,同意;但如果多层推荐connect_by_isleaf = 1
E: 一共才三层关系,所以可以用传统的JOIN关联
F: 传统的JOIN关联,但关联关系表达错误,正确方式为E

使用道具 举报

回复
论坛徽章:
368
生肖徽章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-6-12 09:52 | 显示全部楼层
如果层次不固定:

SQL> select substr(sys_connect_by_path(name,','),2) res
  2     from qz_geography t
  3    where connect_by_isleaf = 1
  4  connect by id = prior parent_id
  5    start with name in ( select name
  6                           from qz_geography t
  7                          where connect_by_isleaf = 1
  8                        connect by prior id = parent_id
  9                          start with parent_id is null)
10    order by 1
11  /
RES
--------------------------------------------------------------------------------
Fleestedt,Niedersachsen,Germany
Long Beach,California,USA
Los Angeles,California,USA
Wayland,New York,USA
Zossen,Brandenburg,Germany

使用道具 举报

回复
认证徽章
论坛徽章:
49
秀才
日期:2018-12-05 13:58:28秀才
日期:2018-11-13 14:57:47秀才
日期:2018-11-13 14:57:26秀才
日期:2018-08-31 10:54:12秀才
日期:2018-08-31 10:53:55技术图书徽章
日期:2018-08-31 10:48:56秀才
日期:2018-08-31 10:48:48秀才
日期:2018-08-31 10:48:48秀才
日期:2018-08-31 10:48:48秀才
日期:2018-08-31 10:48:34
发表于 2018-6-12 10:04 | 显示全部楼层
CDE,三楼已经回答了。
补充一下,sys_connect_by_path  ,作用是从start with 开始的地方遍历,记录下遍历的各个节点,加上设置的分隔符,拼接成一个新的语句。

使用道具 举报

回复
论坛徽章:
368
生肖徽章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-6-12 10:04 | 显示全部楼层
CTE 也可以:

SQL> with t(lvl,namelist,id,parent_id) as ( select 1,
  2                                                name,
  3                                                id,
  4                                                g.parent_id
  5                                           from qz_geography g
  6                                          where type = 'City'
  7                                          union all
  8                                         select t.lvl + 1,
  9                                                t.namelist||','||s.name,
10                                                s.id,
11                                                s.parent_id
12                                           from t,qz_geography s
13                                          where t.parent_id = s.id )
14  select namelist from t where t.lvl = 3 order by 1
15  /
NAMELIST
--------------------------------------------------------------------------------
Fleestedt,Niedersachsen,Germany
Long Beach,California,USA
Los Angeles,California,USA
Wayland,New York,USA
Zossen,Brandenburg,Germany

使用道具 举报

回复
论坛徽章:
9
秀才
日期:2018-07-23 13:44:01秀才
日期:2018-07-23 13:44:16秀才
日期:2018-07-23 13:44:16技术图书徽章
日期:2018-07-23 13:44:34秀才
日期:2018-07-23 13:50:14秀才
日期:2018-07-23 13:50:14秀才
日期:2018-07-23 14:00:48技术图书徽章
日期:2018-07-23 14:01:23秀才
日期:2018-07-23 14:01:23
发表于 2018-6-12 10:49 | 显示全部楼层
答案:CDE
AB:AB错在connect by parent_id = prior id,由Country为父节点向子节点连接。
C:connect by id = prior parent_id对的,以City为子节点向父节点连接。
D:connect_by_isleaf=1 C的 level=3,从City子节点向Country父节点连接
E:两个Join关联实现
F:关联条件不足,查询出重复记录

使用道具 举报

回复
论坛徽章:
530
奥运会纪念徽章:垒球
日期: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-6-13 04:19 | 显示全部楼层
答案CDE, 3楼得奖。

A: 这是经典分层查询,从树顶部(在本例中为国家)开始,向下遍历到树叶(城市)。 只保留输出的叶子给了我们正确的5个城市,但由于我们是从顶部开始,所以SYS_CONNECT_BY_PATH在这个错误的输出中给了我们“国家,州,城市”:

CITY
------------------------------------------------------------
Germany, Brandenburg, Zossen
Germany, Niedersachsen, Fleestedt
USA, California, Long Beach
USA, California, Los Angeles
USA, New York, Wayland


B:
在这种情况下,从类型为国家开始,和从父节点为NULL开始是相同的。然后我们试图通过为SYS_CONNECT_BY_PATH指定ORDER BY来“修复”前一选项的问题。但它不是一个像LIST_AGG那样支持排序的聚合函数——根据设计,SYS_CONNECT_BY_PATH按分层的顺序返回当前分支。由于这是不受支持的语法,解析器期望看到的是终止的括号不是ORDER BY,于是报错
ORA-00907: missing right parenthesis.

C: 但是如果我们从树的底部开始并使用CONNECT BY向上遍历至国家级别,则SYS_CONNECT_BY_PATH将为我们提供所需的排序。既然我们知道在这种情况下,我们从城市开始,往上进入州,然后再向上到国家,我们可以简单地过滤LEVEL = 3以获得期望的结果。

D:(推荐)
由于我们从底部开始向上遍历,因此国家实际上成为倒置树的叶子,所以我们可以使用CONNECT_BY_ISLEAF而不是LEVEL进行过滤,并获得相同的正确结果。这样做的优点是,如果结构不那么严密(例如有些国家没有州,有些州有地区),那么即使有些城市在二级,其他的在三级,一些在四级,这个选项也能工作。

E: 鉴于我们的数据拥有严格的城市,州,国家这种结构,我们也可以选择简单连接而不是分层查询并获得相同的正确结果。

F: 但是在这里我们并没有按照ID / PARENT_ID关系来连接,相反我们连接了所有4个州的所有5个城市,所有2个国家,给了我们这个错误的输出和全部40个组合:

CITY
------------------------------------------------------------
Fleestedt, Brandenburg, Germany
Fleestedt, Brandenburg, USA
Fleestedt, California, Germany
Fleestedt, California, USA
Fleestedt, New York, Germany
Fleestedt, New York, USA
Fleestedt, Niedersachsen, Germany
Fleestedt, Niedersachsen, USA
Long Beach, Brandenburg, Germany
Long Beach, Brandenburg, USA
Long Beach, California, Germany
Long Beach, California, USA
Long Beach, New York, Germany
Long Beach, New York, USA
Long Beach, Niedersachsen, Germany
Long Beach, Niedersachsen, USA
Los Angeles, Brandenburg, Germany
Los Angeles, Brandenburg, USA
Los Angeles, California, Germany
Los Angeles, California, USA
Los Angeles, New York, Germany
Los Angeles, New York, USA
Los Angeles, Niedersachsen, Germany
Los Angeles, Niedersachsen, USA
Wayland, Brandenburg, Germany
Wayland, Brandenburg, USA
Wayland, California, Germany
Wayland, California, USA
Wayland, New York, Germany
Wayland, New York, USA
Wayland, Niedersachsen, Germany
Wayland, Niedersachsen, USA
Zossen, Brandenburg, Germany
Zossen, Brandenburg, USA
Zossen, California, Germany
Zossen, California, USA
Zossen, New York, Germany
Zossen, New York, USA
Zossen, Niedersachsen, Germany
Zossen, Niedersachsen, USA

使用道具 举报

回复

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

本版积分规则 发表回复

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