[HacKerRank SQL] Ollivander's Inventory - MYSQL

Input Format

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

• Wands:

  

• Wands_Property: 'code'와 'age'는 1:1 대응 관계, 즉 (code, age) 쌍은 다른 쌍과 중복되지 않음

  

 

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.

 

아래의 조건으로 id와 age, coins_needed 그리고 power 조회하기

- 같은 power, age 라면, 가장 낮은 coins_needed 1개만 조회

- 이를 power 내림차순, 동일 power일 경우, age 내림차순

 

예를들어 입력값으로 하기 2개 값이 주어진다면

id	age	coins_needed	power
1	20	1000	50
2	20	5000	50
3	10	3000	50
4	55	2500	30

출력값은 다음과 같음

id	age	coins_needed	power
1	20	1000	50
3	10	3000	50
4	55	2500	30

 

SELECT
    W1.id,
    TEMP.MYAGE,
    TEMP.MYCOIN,
    TEMP.MYPOWER
FROM Wands AS W1 
    INNER JOIN (    
    		-- 최소값을 가지고 있는 쿼리 결과, "TEMP"로 칭함
                SELECT
                    W.code AS MYCODE,
                    WP.age AS MYAGE,
                    MIN(W.coins_needed) AS MYCOIN,
                    W.power AS MYPOWER
                FROM 
                    Wands AS W
                    INNER JOIN Wands_Property AS WP
                    ON W.code = WP.code
                WHERE 
                    WP.is_evil = 0
                GROUP BY 
                    W.code,
                    WP.age,
                    W.power
                ) AS TEMP
    -- 유일쌍인 (code, age) 그리고 추가적으로 coins_needed와도 매칭시킴
    ON W1.coins_needed = TEMP.MYCOIN AND W1.power = TEMP.MYPOWER AND W1.code = TEMP.MYCODE
ORDER BY
    TEMP.MYPOWER DESC, 
    TEMP.MYAGE DESC