hackerrank - Basic Join - Ollivander's Inventory

Harry Potter and his friends are at Ollivander's with Ron, finally replacing Charlie's old broken wand.

Hermione decides the best way to choose is by determining the minimum number of gold galleons needed to buy each non-evil wand of high power and age. Write a query to print the id, age, coins_needed, and power of the wands that Ron's interested in, sorted in order of descending power. If more than one wand has same power, sort the result in order of descending age.

Input Format

The following tables contain data on the wands in Ollivander's inventory:

• Wands: The id is the id of the wand, code is the code of the wand, coins_needed is the total number of gold galleons needed to buy the wand, and power denotes the quality of the wand (the higher the power, the better the wand is).

• Wands_Property: The code is the code of the wand, age is the age of the wand, and is_evil denotes whether the wand is good for the dark arts. If the value of is_evil is 0, it means that the wand is not evil. The mapping between code and age is one-one, meaning that if there are two pairs,  and , then  and .

---

Sample Input

Wands Table:Wands_Property Table:

Sample Output

9 45 1647 10
12 17 9897 10
1 20 3688 8
15 40 6018 7
19 20 7651 6
11 40 7587 5
10 20 504 5
18 40 3312 3
20 17 5689 3
5 45 6020 2
14 40 5408 1

---

---

这是一个self join的问题，题目的意思是当power和age相同的情况下选择最小的coins needed，同时is evil为0。也就是说，这两个table需要先inner join，摘选出non evil的数据。合并后的表格中，如果power 和age相同，则选择最小的coins needed，而同时，要提取所对应的ID。如果使用group by power and age，则这里的id不能用min或者max提取出对应的id。因此合并后的表格只需要提取出相同power和age时最小coins needed即可。

Select w.id, p.age, w.coins_needed, w.power
From Wands w inner join Wands_Property p on
w.code = p.code
Where p.is_evil = 0 and w.coins_needed =
(
Select min(coins_needed) as coins_needed
from Wands w1 inner join Wands_Property p1 on
w1.code = p1.code
Where w.power = w1.power and p.age = p1.age
)
Order by w.power desc, p.age desc;

上面的code中w.coins_needed = 中的‘='换成‘in'同样成立。