最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正)，其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。
每两周的优胜者可获得itpub奖励的技术图书一本。
以往旧题索引：
http://www.itpub.net/forum.php?m ... eid&typeid=1808
原始出处：
http://www.plsqlchallenge.com/
作者：
Kim Berg Hansen
运行环境：SQLPLUS, SERVEROUTPUT已打开
注：本题给出答案时候要求给予简要说明才能得到奖品
我有一张汽车的表，里面有诸如厂家、型号、类型等属性。不幸的是应用设计者喜欢EAV(Entity-Attribute-Value)数据模型，在这种模型中，这种属性被独立地存在一张表中，每个属性的name/value为一行：
create table plch_cars (
id
integer primary key
, namevarchar2(30)
)
/
create table plch_attributes (
car_idinteger references plch_cars
, attribute varchar2(10)
, value varchar2(30)
, constraint plch_attributes_pk primary key (car_id, attribute)
)
/
insert into plch_cars values (1, 'Model X SUV from Tesla')
/
insert into plch_cars values (2, 'Edge SUV from Ford')
/
insert into plch_cars values (3, 'Malibu Sedan from Chevrolet')
/
insert into plch_attributes values (1, 'MAKE' , 'Tesla')
/
insert into plch_attributes values (1, 'MODEL', 'Model X')
/
insert into plch_attributes values (1, 'TYPE' , 'SUV')
/
insert into plch_attributes values (2, 'MAKE' , 'Ford')
/
insert into plch_attributes values (2, 'MODEL', 'Edge')
/
insert into plch_attributes values (2, 'TYPE' , 'SUV')
/
insert into plch_attributes values (3, 'MAKE' , 'Chevrolet')
/
insert into plch_attributes values (3, 'MODEL', 'Malibu')
/
insert into plch_attributes values (3, 'TYPE' , 'Sedan')
/
commit
/
应用开发者保证这个应用会强制每辆车的每个MAKE, MODEL 和 TYPE属性都恰好有一行记录（既不会是零行，也不会多于一行）（我没有SQL断言，我相信他们是对的）
我想要一个报表显示汽车（id和名字），按照"type, make, model"顺序排列，不分大小写。如果这三个属性是car表中的三个列，那我就简单地这么写（假设的，并不是真的能用）：
select id, name
from plch_cars
order by upper(type), upper(make), upper(model)
/
但是既然我没有这样的列，我需要写个查询来模拟这种行为，从plch_attributes表中得到需要排序的属性值。
哪些选项包含的查询会把汽车按照他们的属性值以上述方式排序，让我得到这个输出：
ID NAME
---------- ------------------------------
3 Malibu Sedan from Chevrolet
2 Edge SUV from Ford
1 Model X SUV from Tesla
(A)
select c.id, c.name
from plch_cars c
order by ( select upper(a.value)
from plch_attributes a
where a.car_id = c.id
and a.attribute = 'TYPE' )
, ( select upper(a.value)
from plch_attributes a
where a.car_id = c.id
and a.attribute = 'MAKE' )
, ( select upper(a.value)
from plch_attributes a
where a.car_id = c.id
and a.attribute = 'MODEL' )
/
(B)
select c.id, c.name
from plch_cars c
order by (
select listagg(rpad(upper(a.value), 30)) within group (
order by case a.attribute
when 'TYPE'then 1
when 'MAKE'then 2
when 'MODEL' then 3
end
)
from plch_attributes a
where a.car_id = c.id
and a.attribute in ('MAKE', 'MODEL', 'TYPE')
)
/
(C)
select c.id, c.name
from plch_cars c
join (
select a.car_id
, listagg(rpad(upper(a.value), 30)) within group (
order by case a.attribute
when 'TYPE'then 1
when 'MAKE'then 2
when 'MODEL' then 3
end
) order_string
from plch_attributes a
where a.attribute in ('MAKE', 'MODEL', 'TYPE')
group by a.car_id
) ordering
on ordering.car_id = c.id
order by ordering.order_string
/
(D)
select c.id, c.name
from plch_cars c
join (
select car_id
, row_number() over (order by type, make, model) rn
from plch_attributes a
pivot (
max(upper(value))
for attribute in (
'MAKE'as make
, 'MODEL' as model
, 'TYPE'as type
)
)
) ordering
on ordering.car_id = c.id
order by ordering.rn
/
(E)
select c.id, c.name
from plch_cars c
join plch_attributes make
on make.car_id = c.id
and make.attribute = 'MAKE'
join plch_attributes model
on model.car_id = c.id
and model.attribute = 'MODEL'
join plch_attributes type
on type.car_id = c.id
and type.attribute = 'TYPE'
order by upper(type.value)
, upper(make.value)
, upper(model.value)
/
(F)
select c.id, max(c.name) as name
from plch_cars c
join plch_attributes a
on a.car_id = c.id
and a.attribute in ('MAKE', 'MODEL', 'TYPE')
group by c.id
order by max(case a.attribute when 'TYPE'then upper(a.value) end)
, max(case a.attribute when 'MAKE'then upper(a.value) end)
, max(case a.attribute when 'MODEL' then upper(a.value) end)
/
(G)
select id, name
from plch_cars c
join plch_attributes a
on a.car_id = c.id
and a.attribute in ('MAKE', 'MODEL', 'TYPE')
pivot (
max(upper(value))
for attribute in (
'MAKE'as make
, 'MODEL' as model
, 'TYPE'as type
)
)
order by type
, make
, model
/
|
