[MySQL/Hackerrank] Ollivander's Inventory

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, (code1, age1) and (code2, age2), then code1 != code2 and age1 != age2.

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

 

/*
table - Wands, Wands_Property
item -  id, age, coins_needed, and power
order - power desc, age desc
   is_evil is 0 -> not evil
   code and age is one-one
   
*/ 




SELECT A.id
    , B.age
    , A.coins_needed
    , A.power
FROM Wands A
    JOIN (
        SELECT *
        FROM Wands_Property
        WHERE is_evil = 0
    ) B
    ON A.code = B.code
    JOIN (
        SELECT B.age, A.power
            , MIN(coins_needed) AS min_coins_needed
        FROM Wands A
            JOIN Wands_Property B
            ON A.code = B.code
        WHERE B.is_evil = 0
        GROUP BY B.age, A.power
    ) C
    ON (A.power = C.power AND B.age = C.age)
WHERE A.coins_needed = C.min_coins_needed
ORDER BY A.power DESC, B.age DESC

 

영어를 못해서인지... 질문 이해를 못해서 너무 어렵다...ㅜㅜ

우선 Wands 테이블과 Wands_Property 테이블을 join해서 power, age 순서로 정렬하는 것이 기본이다.

샘플로 예를 든다면 이 경우 power, age가 동일한 여러개의 지팡이가 나오게 되는데

이 때에는 coins_needed가 작은 금액인 데이터를 추출해야 한다.

ms-sql의 경우에는 partition by 구문 사용해서 power, age로 파티션을 나눈다음 시퀀스를 붙여서 하던데....

복잡하고 어렵고.....; 

그냥 age, power 기준으로 MIN(coins_needed)을 구한다음 join 할 때 조건으로 넣어주었다.

어차피 coins_needed는 min값 말고는 필요없는 값이므로.... 아 어지럽다 ㅠ