最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正)，其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。
每两周的优胜者可获得itpub奖励的技术图书一本。
以往旧题索引：
/forum.php?m ... eid&typeid=1808
原始出处：
/
作者： Kim Berg Hansen
运行环境：SQLPLUS, SERVEROUTPUT已打开
注：本题给出答案时候要求给予简要说明才能得到奖品
注：本题的执行环境为12c或更高版本
我有这些博客和博客文章的表：
create table qz_blogs (
blog_idintegerprimary key
, name varchar2(20)
)
/
create table qz_posts (
blog_idintegerreferences 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 '-07-01', 'Mixing Spices')
/
insert into qz_posts values (10, date '-07-03', 'Jambalaya Hot')
/
insert into qz_posts values (30, date '-07-05', 'Oval OakTable')
/
commit
/
我想要一张博客的列表，上面显示每个博客的最近一篇文章。这个清单必须以最后发表的时间的降序排列，所以有最新文章的博客排在列表的最前面。
哪些选项包含了一个查询能够产生这样一个清单：
BLOG_ID NAMELAST_POSTLAST_TITLE
---------- -------------------- ---------- --------------------
30 DIY Furniture -07-05 Oval OakTable
10 Cajun Cooking -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
/
![lateral函数oracle [026]12c lateral语法.txt](https://www.dyjz.cc/uploadfile/img/15/555/9ff35671199a2b8dbf33bb4e620206e0.jpg)
