lateral函数oracle,PL/SQL Challenge 每日一题：2017-7-20 12c新功能：Lateral 内联视图

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

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

以往旧题索引：

http://www.itpub.net/forum.php?m ... eid&typeid=1808

原始出处：

http://www.plsqlchallenge.com/

作者：        Kim Berg Hansen

运行环境：SQLPLUS, SERVEROUTPUT已打开

注：本题给出答案时候要求给予简要说明才能得到奖品

注：本题的执行环境为12c或更高版本

我有这些博客和博客文章的表：

create table qz_blogs (

blog_id  integer  primary key

, name     varchar2(20)

)

/

create table qz_posts (

blog_id  integer  references qz_blogs

, posted   date

, title    varchar2(20)

)

/

insert into qz_blogs values (10, 'Cajun Cooking')

/

insert into qz_blogs values (20, 'Homemade Knitwear')

/

insert into qz_blogs values (30, 'DIY Furniture')

/

insert into qz_posts values (10, date '2017-07-01', 'Mixing Spices')

/

insert into qz_posts values (10, date '2017-07-03', 'Jambalaya Hot')

/

insert into qz_posts values (30, date '2017-07-05', 'Oval OakTable')

/

commit

/

我想要一张博客的列表，上面显示每个博客的最近一篇文章。这个清单必须以最后发表的时间的降序排列，所以有最新文章的博客排在列表的最前面。

哪些选项包含了一个查询能够产生这样一个清单：

BLOG_ID NAME                 LAST_POST  LAST_TITLE

---------- -------------------- ---------- --------------------

30 DIY Furniture        2017-07-05 Oval OakTable

10 Cajun Cooking        2017-07-03 Jambalaya Hot

20 Homemade Knitwear

(A)

select b.blog_id

, b.name

, (select max(p.posted)

from qz_posts p

where p.blog_id = b.blog_id

) as last_post

, (select max(p.title) keep (dense_rank last order by p.posted)

from qz_posts p

where p.blog_id = b.blog_id

) as last_title

from qz_blogs b

order by last_post desc nulls last

/

(B)

select blog_id, name, posted as last_post, title as last_title

from (

select b.blog_id

, b.name

, p.posted

, p.title

, row_number() over (

partition by b.blog_id order by p.posted desc

) as rn

from qz_blogs b

left outer join qz_posts p

on p.blog_id = b.blog_id

)

where rn = 1

order by last_post desc nulls last

/

(C)

select b.blog_id

, b.name

, last_p.posted as last_post

, last_p.title as last_title

from qz_blogs b

left outer join lateral (

select p.posted

, p.title

from qz_posts p

where p.blog_id = b.blog_id

order by p.posted desc

fetch first 1 row only

) last_p

on 1 = 1

order by last_post desc nulls last

/

(D)

select b.blog_id

, b.name

, last_p.posted as last_post

, last_p.title as last_title

from qz_blogs b

left outer join (

select blog_id, posted, title

from (

select p.blog_id

, p.posted

, p.title

, row_number() over (

partition by p.blog_id order by p.posted desc

) as rn

from qz_posts p

)

where rn = 1

) last_p

on last_p.blog_id = b.blog_id

order by last_post desc nulls last

/

(E)

select b.blog_id

, b.name

, last_p.last_post

, last_p.last_title

from qz_blogs b

left outer join (

select p.blog_id

, max(p.posted) as last_post

, max(p.title) keep (

dense_rank last order by p.posted

) as last_title

from qz_posts p

group by p.blog_id

) last_p

on last_p.blog_id = b.blog_id

order by last_post desc nulls last

/