**答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正)，其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。
每两周的优胜者可获得itpub奖励的技术图书一本。
以往旧题索引：
http://www.itpub.net/forum.php?m ... eid&typeid=1808
原始出处：
https://devgym.oracle.com/
作者：Kim Berg Hansen
运行环境：SQLPLUS, SERVEROUTPUT已打开, **版本要求：12.1
注：本题给出答案时候要求给予简要说明才能得到奖品
我有一张表，存储着每个国家每年的销售统计：
create table qz_stats (
country varchar2(2)
, yearinteger
, amountinteger not null
, primary key (country, year)
);
insert into qz_stats values ('US', 2019, 11000);
insert into qz_stats values ('US', 2020, 15000);
insert into qz_stats values ('US', 2021, 13000);
insert into qz_stats values ('DK', 2019, 12000);
insert into qz_stats values ('DK', 2020,9000);
insert into qz_stats values ('DK', 2021, 11000);
insert into qz_stats values ('NL', 2019,8000);
insert into qz_stats values ('NL', 2020, 11000);
insert into qz_stats values ('NL', 2021, 14000);
commit;
我想要以如下的顺序来输出这张表的数据：
国家必须以它们首年的销售额的降序来排序。
如果国家之间有平局，则平局的国家以字母顺序排序。
在每个国家中，数据行以年份排序。
哪些选项包含了一个查询，可以执行不出错并且产生这个所需的输出：
CO YEAR AMOUNT
-- ---------- ----------
DK 201912000
DK 2020 9000
DK 202111000
US 201911000
US 202015000
US 202113000
NL 2019 8000
NL 202011000
NL 202114000
(A)
select
country, year, amount
from qz_stats
order by
first_value(amount) over (partition by country order by year) desc
, country
, year;
(B)
select
country, year, amount
from (
select
country, year, amount
, first_value(amount) over (partition by country order by year) as first_year_amount
from qz_stats
)
order by
first_year_amount desc
, country
, year;
(C)
select
country, year, amount
, first_value(amount) over (partition by country order by year) as first_year_amount
from qz_stats
order by
first_year_amount desc
, country
, year;
(D)
select
country, year, amount
from qz_stats
order by
(
select amount
from qz_stats stats2
where stats2.country = qz_stats.country
order by year
fetch first 1 row only
) desc
, country
, year;
(E)
select
qz_stats.country, qz_stats.year, qz_stats.amount
from qz_stats
join (
select
country
, max(amount) keep (dense_rank first order by year) as first_year_amount
from qz_stats
group by country
) stats2
on stats2.country = qz_stats.country
order by
stats2.first_year_amount desc
, qz_stats.country
, qz_stats.year;
|
